Level Up your Power BI Game with Field Parameters! (with Nikola Ilic)

[Music] [Music] [Music] [Music] [Music] [Music] [Music] [Music] [Music] [Music] [Music] [Music] [Music] [Music] [Music] n [Music] [Music] [Music] [Music] [Music] [Music] [Music] n [Music] [Music] hi Nicola good morning for me and good evening to you hi hi Reed yeah as you said good evening for me and good morning to you and hi everyone who joined the the stream yeah thanks for coming back on this time thankfully I finally had to to get the new motor replace and everything fixed so internet is running like uh like a well-oiled engine at this point um but it it was quite literally that the people in the US really like to as providers um like to make modems obsolete about a year to two after you buy them and like oh no we're switching to a new protocol your modem is not compatible anymore you have to go buy a new one now so oh doesn't sound uh uh very very much fun yeah I remember last time uh there there were issues yeah so hopefully uh internet Gods will be with us today oh yeah we we should be golden outside of like literally the power going out that's the only thing that that should cause any problems um I also see that uh are are you wearing your your shirt as as a bit of excitement to be headed to Vegas soon or yeah that was by coincidence but I realized uh uh yeah uh that we have stream today and I realized that we'll see each other in Vegas next month on fabric conference so it was really a coincidence but yeah it's yeah you're right I'm I'm already excited about that yeah yeah it's uh going to be a big conference first time I've done one honestly in Vegas even though that's a very common spot for a lot of conferences usually the tech conferences typically don't end up there versus like Florida or Seattle but it'll be a lot of fun for sure and you're you're presenting as well yeah yes I'm presenting together with shabnam uh something about direct Lake yeah yeah nice nice nice yeah I'll be cooh helming one with Kurt berer on uh uh be like fabric architecture so that will be a lot of fun oh nice nice I will definitely attend your session excellent well same hopefully if as long as our aren overlapping yeah yeah yeah hopefully not yeah yeah yeah hopefully not yeah exactly but um to the topic today we uh tried to do this back in early December uh it's on one of my favorite topics personally which is field parameters so I'm very excited uh to see some of the tips tricks and techniques uh I think we'll also probably even have some opportunity to likely answer some questions from the audience uh even on maybe some of the the topics on like c groups versus field parameters which I do think that a lot of people are curious of the wi and wear between the two because there there's a decent amount of overlap on um scenarios that could potentially use either or for those but thankfully we have the flexibility to use both um to get started though U similar to last time if you want to give a brief introduction to yourself for the people less familiar with you either watching now or watching the recording that would be great exactly exactly yeah uh so uh first of all I I just wanted to say hello to to people in audience so and Matias nice to see you all uh I'm Nicola I'm originally from Belgrade in Serbia but yeah for last uh eight years I live in salsburg wonderful City in Austria uh I work as an independent data platform consultant and trainer mostly focusing on Microsoft uh uh data platform Technologies and services uh started back then long time ago with SQL server and this traditional B stack so my background is with relational databases and then I transitioned uh uh to powerbi somewhere in 2017 and since then I'm predominantly working with powerbi if so you say predominantly is there what's the last remaining percentage of things that you work with that's not powerbi out of curiosity yeah I still work with some part of back end with SQL and I really enjoy so SQL is my first love so I still have some clients who are on the SQL Server uh some clients there who are on synaps so it's still some some portion of SQL in my uh day-to-day life and I enjoy it to be honest I keep having to retrain myself to to start to say like Fabric or like powerbi fabric just too to broaden that that that the tool belt you know just to show that I know a few other things besides reporting exactly exactly I mean a lot of things uh lines are blurred so a lot of things are overlapping but uh yeah the more you know the better but it's impossible to know everything so it's so Broad and then it develops so fast that yeah it's impossible to know everything I wish I could remember the somebody a few years ago did a really good LinkedIn post which there was a about 10 call it was a grid of basically little U square square cells that that could fill out and there's probably 15 categories between Python SQL powerbi data modeling and all that you're only given 10 cubes to fill in like that's that's the most expertise you could have so you can either go wide or you could go deep but there's only so much you could fill in any section and you have to just consider am I breadth or depth and which which topics and subjects am I going to be more an expert on um but I I really like that visual analogy to help just describe that like this is the Microsoft ecosystem you can really only be an expert on about this many or you can be a an intermediate expert on like this or or a beginner um person on this many but you have to choose carefully yeah yeah yeah yeah exactly that that's very true that's very true and it's now especially with fabric uh where you have yeah a lot of different ways to do the same thing and uh yeah based on on on your skills and uh uh yeah your experience uh it's it really opens up a lot of different possibilities yeah and if you'd like as well um speaking of powerbi and everything um I can flip over to the the slides that I see up and we can also start talking about some of the uh things involving field parameters today as well yeah absolutely absolutely so uhh okay so I'll then skip presentation because yeah the the most important thing I forgot to tell is that uh I'm father of two kids and uh uh Drew football and Bara fun uh yeah as you may conclude if you look at this photo that was made when I was a lot younger uh at uh campno former former Stadium of Barcelona okay so I'll jump in then straight into the action and uh yeah we can cheat chat during the the the presentation and if there are any questions from audience of course we can uh yeah interrupt you feel feel free to interrupt me and yeah we can we can try to answer them first and most important thing that I would like for everyone to remember is that uh field parameters is still uh a preview feature so it appeared I think almost two years ago somewhere in May 2022 and it's still in preview I don't know why recently I had a a short discussion with Jay uh from uh parb uh uh team in Microsoft and uh he promised that he will come back to me so we can chat why this feature is still in in public preview and not generally available after almost two years and it works pretty much pretty fine so uh I'm really curious to to find out more about that and as soon as I hear if it's not under NDA I will share with with all of you but keep in mind that uh if you open parb Desktop and you don't see field parameters uh when you open parb Desktop make sure to first go to options and settings to options then preview features and then enable uh and then enable field parameters that's the main prerequisite to use them in your powerbi reports yep okay yeah so uh in dark ages of powerbi and yes powerbi really had Dark Ages uh although it's hard to believe now at 2024 but uh if you wanted to dynamically switch the axis of the visual which was very common and still is very common uh business requirement for example between I don't know you have products and countries that you want to dynamically switch axis of your visual uh between products and countries to show different uh calculations you had to write I like to call this exotic decks I will show you in a few minutes why I consider this exotic decks using uh uh various tricks and techniques with uh three test function uh maybe I can show show you what I'm talking about just to just to have a feeling about our pain before field parameters uh came here okay so here is parb desktop okay so this is the use case I I mentioned so imagine that this is uh yeah this is just an uh regular bar chart Visual and uh now at this moment I have both uh countries and products displayed so the idea is if I select or if user selects countries only countries should be displayed here on this uh uh y AIS and vice versa if I select Brands only brands should be displayed here now for this to work before uh field parameters let me show you what we had to do so let's first Zoom it a little bit I think it's it's visible so this is a helper table that I created uh taking all distinct values from uh my product table brand name column and all distinct values from uh region country name which is uh which contains uh values for uh different countries I'm using I think this is contos or Adventure Works uh it it doesn't matter but basically I'm using those uh I'm taking those distinct values then I'm making cartisian product by by using cross joint and then finally I'm uh making Union of all these distinct values cross jointed so in this case I create this helper table and I will use this these values from this table let me show you here how it looks like so I have my value and I have uh axis column so you see all the distinct values from those two columns and I put uh this into the slicer and here on my uh uh Y axis I'm putting this value from from this helper table that's first part of of the solution now if I put a regular sales amount uh measure here just get a little more space here nothing happens yeah because this uh there is no uh there is no any context and any relationship between Brands and countries that I'm using in this calculated table so what I what we had to do is as I said writing some exotic decks in this case I'm leveraging three test function to create virtual relationships between uh my newly created table Brands and countries and uh those physical tables that exist in my uh data model in this case it's online sales and in this case I'm creating a virtual relationship between uh Brands and countries and geography and in this case between Brands and countries and products I don't want to bother you too much you see the complexity of decks just for two different uh attributes that we want to use to dynamically switch uh within the visual that's why I said this is exotic decks another use case is that this was for dynamically switching the attributes on Y axis but also users almost always want to have a possibility to to dynamically choose what should be display which calculation should be displayed in the certain context so that means for example they want to see sales amount regular sales amount or they want to see uh uh running totals like sales amount year to date and again what we did in those uh scenarios let's say that okay this is the the another use case and paino before field parameters uh uh enter the stage so here is the idea in this case I'm showing total sales amount and the idea is to enable users when they click on the slicer to select instead of sales amount to be here to be displayed sales amount year to dat so what we had to do we had to uh create again helper tables so for example I go and create uh here uh let's say ID and let's say this one will be called time frame in this case I want to switch between two different values this one is sales amount and this one is sales amount year to date let's call this one time frame I will load this to data model so again helper table again bloating our data model okay and time frame here it is then I need to capture what user selects uh in this slicer that I will put in the report so this one should be like selected time frame and it equals minan of time frame ID so in case that nothing is selected it will by default return the minimum value which is one in our case sales amount so another measure let's say helper measure and for example now if I put a slicer on the report page and let's say time frame and uh uh now the idea is when user clicks on sales amount here this this Valu should be displayed if I click on sales amount year to date though this should uh display running total so it's a typical uh uh concept of disconnected tables I'm sure if you used to work with parb and if you're still working with parb you had this requirement at least once uh I can bet my life uh uh on that so now the next thing we need to create is our uh uh another measure which will essentially use uh Lo switch logic we will leverage switch logic to uh dynamically move between those two uh between those two uh measures so let's call this one sales amount time frame and it equals so we need to switch and we need to switch selected time frame and in case it's one we want to return sales amount if it's two then we want to return sales amount year to date just for the sake of proper naming I will remove this table references and this is our sales amount time frame measure so when I put it here instead of regular sales amount what happens at first moment nothing but if I click here on sales amount year to date this one will now show me running totals so now you're probably wondering this is a session on field parameters why did he show us this because I wanted to feel the pain our pain before field parameters so you can then start appreciate appreciating uh uh the real benefit that we got with field parameters so now you saw those two examples which are Super common so they don't solve some niche uh uh use cases or scenarios this the both of these requests are very common when you are developing Barbi reports and it was really like this solving those challenges like this now with field parameters I will move to another report page that looks exactly the same and I want to show you how you can solve these two uh requests without uh any deck and just by using field parameters so I'll go to modeling new parameter and I will select Fields uh Fields parameter let's call this one param test so in this case we had country Orion country name and we had um brand name I think it's in product yes and by default this option adds lcer to this page is checked this means when I click on create powerbi will automatically create slicer on your report page and put those fields that are that are listed in this area there so I'll keep this uh uh selection and say uh create this slicer so we have those two region country name and brand name now in this case instead of brand name on y-axis which is static value basically I'll go to parm 3 test and put this instead of a brand name and what happens like in previous case when we had this helper table and we had this measure with three tests now I'm just clicking here and there and switching between the values that's problem number one solved in 30 seconds let's solve problem number two so again I'll create let's create a new one so I'll go to new parameter and Fields I'll call this one uh selection and we had sales amount so it's not limited to uh uh Dimension attributes you can use also measures Dux measures uh uh as as uh uh part of your field parameters again I want to add a slicer to a report page and here it is now the only thing we need to do is to switch this static value with the value from field parameter so premi parameter selection instead of sales amount I'll do this and let me just show you now it's sales amount now it's sales amount here to date you don't need to do any additional formatting so it will automatically pick up the formatting that you set for this underlying measure let me show you for example let's say that instead of currency for sales amount year to date for whatever reason I want to or let's just change the currency to for example Euro and you see that it automatically propagates it automatically applies the formating you applied to the original measure so no additional uh uh headache and reformating of your measures so instead of all this stuff that we used to do previously now it's done in literally two minutes you have a solution for something that most of your us users will ask you to do and that's why I call and why I consider field parameters a game changer uh I would say many times we heard during the powerbi development Evolution we we heard this uh uh words like Game Changer this the the data analysis will not be the same after this feature and so on and so on uh some of them live to their expectations some of them not obviously uh reasons are different of course but let's be honest and admit that most of these features were just a nice addition nothing more nothing less or even if they were important maybe they were solving just one problem so no matter how big the problem is it's just a single problem field parameters in my opinion can solve a whole range of different challenges and and uh literally opens up as you will see and you will conclude after watching uh this session and maybe uh uh the video that Reed did last week comparing field parameters and uh calculation groups you will you will come up I'm sure with many more potential use cases uh and one thing I just want to to mention on on part of the game changing part because that field parameters have two like primary components they can they they they universally switch Fields but more specifically they can switch uh metrics or they can switch categorical columns on your dimensional tables and most anybody who spent a little bit of time with Dax or not anybody but a lot of people have at some point looked up the blog post where you can have a a disconnected or an island table that has you know actual or budget and you know you have a just a switch statement in a Dax to switch between the two so it wasn't it wasn't easy but it wasn't that difficult to switch from measure a to measure B the one thing is um you you couldn't have the dynamic formatting with it because you had a single measure but otherwise you could mostly accomplish that the really hard thing to do as you just showed was having a custom table treat as I personally would build a I would have a union table with a bunch of inactive relationships and keys but like actually modeling to switch the x or y AIS categorically that's the really challenge to like impossible for most people part and I I would say of all the game changer Parts categorical switches on a visual I I think is to me the biggest game changer by and far in the field parameters it's like the other one was an evolution it made switching between measures easier this one was it went from hard mode to beginner mode uh with that stuff and you don't even have to touch the code there is no me there's no code writing to use it you just select the two columns and boom there you go so I that's my favorite part yeah yeah yeah yeah yeah exactly that's true that's true that's true so uh as I said uh uh yeah many times we heard but and as I said they open a whole new world of uh perspective of and possibilities not just from data visualization perspective but also and what's what Reed just mentioned that what is even more important in my opinion from a data modeling point of view uh I will show you in a few minutes in one demo what I'm talking about but before being able to get the maximum from the field parameters we need to make ourselves a little bit familiar so I gave you a glimpse of field parameters but I want you to understand how this uh feature Works behind the scenes uh in a nutshell field parameters enable you to perform two different actions dynamically change the attribute or category for slicing and dicing the data in the visual meaning dynamically switch between different columns and the second thing dynamically change the metrics that are displayed in the visual meaning dynamically switch between different measures and the fair point is Nicola we could have done this before field parameters as well yes that's true but instead of three test complexity or writing these switch statements and disconnected tables you can now set everything up as you saw with just a few clicks uh without writing and I think this is the the benefit the biggest benefit without writing a single line of DX codes you don't need to touch the codee at all uh under the hood of field parameters quick look under the hood of field parameters and what happens once you create them so once we Drag The Columns uh and or measures in field parameters window powerbi will automatically create a new table in our data model uh you may also choose to create a slicer uh this is the checkbox that I shown you which will contain all the values from the field parameters and this slicer will be also part of your uh report page this table this field parameters table consists of three built-in columns uh one is visible to the end user while remaining two are hidden by default uh the first one is the name of the column that's going to be displayed in the slicer that's essentially that's the label which is exposed to a consumer you can go and change the name of the column later without needing to change uh the whole logic in the background so if we go to our previous example and first of all I want to show you uh how this table looks so parm selection you see that there are two columns that are hidden we will come up to to explain what why are why they are here but they are hidden and this one is uh visible so as I said you can change uh for example here uh Tre test let's go here and if I click on the table and let me expand formula bar so instead of region country name this is very ugly and user not so user friendly so I will I can call call this one country and this one I can call just brand for examp example and if I confirm this and close it you see that now our names were changed to more user friendly like country and brand uh this is possible because of the second column in the table which is called fields and this column uh relies on a name of Dex function name of function Returns the fully qualified name of the model object why is this important uh let's say that you want to change just the column name as I did from uh brand name to brand or region country name to Country and you can do that without violating the field parameters structure because the name of function will still point to uh uh to the new name of the object and displayed name stays unchanged but it will refer to the same underlying object uh in your semantic model and there is a third column which is a numeric one and it represents the order of the elements within field parameters starting from zero so these two three are provided out of the box once you create field parameters but because this is nothing else but a table regular calculated almost regular calculated table you can also manually extend this table with additional columns if you're wondering why this might be interesting let me show you a quick demo so I'll go back this up I I just wanted to to call it this the the the sentence that you mentioned where it's almost a regular Dax calculated table if you if you take the code copy and paste it and like add that exact same Dax calculated table and tabular editor it won't work the same as a field parameter there is some extra magic that is done from powerbi desktop specifically that like that flags that as a table so just copying the code over and duplicating it that just becomes a static tax calculated table versus an actual field parameters so this can only added today inbi desktop it has to be added from desktop because you have to add a physical cach table to the model anytime you make model changes you cannot do that in the service you um outside of some preview features you have to do it from powerbi desktop exactly that yeah that's a great Point that's why I wanted to say it's like a regular table but it's not like a regular table almost like a regular table you can do a lot of things like you do with regular calculated tables but not not everything as you as you pointed out okay so what I wanted to show you is how you can extend this uh built-in table and let me quickly create uh New Field parameter for this demo so I'll call this one param let's call it uh extend parameter extend and I want to uh use different attributes from different tables let's say I want year and I want month name short from my calendar table then I'll go to geography and let's take continent name and this region country name and let's go to product and let's pick uh brand name and for example uh class name okay so I have six different attributes from three different tables I will hit create and this will generate a new field parameter slicer here uh on the top if I now put here uh this parameter extend instead of brand name let me show you that it works so it's year its month name its continent name so everything works as expected but what if I want to group so in this Cas I have six attributes in real life maybe you will have 10 or 12 more more than that so the idea is to somehow logically group these attributes so I will click on this table definition and you can manually extend this table by adding uh another column basically so in this case for here I will say this belongs to calendar same for my second column which is month name short This one belongs to geography and I'll copy paste this and the last one belongs to product okay this is just to give you an idea what is what is possible okay so once I click to confirm this you will see here we have a new column with this dammy name value four because this is the fourth column uh this is the ordinal number uh of this column so I will just rename it to give it the proper name let's say group and what I can do now I can use this column as any column in other tables to create a hierarchy of fields in my slicer now I have everything nicely grouped calendar uh attributes belong to calendar geography belongs continent and country belongs to geography and product belongs to product that's nice but you can go even further from here and bring some additional life uh I was playing around uh uh a while ago yeah and uh here you are not limited basically to using a static values like I did here so you can also use some Dex functions uh to to uh yeah to to enhance the the user experience in in some cases so unar function index will essentially replace numeric value with visual representation of this number that you provide as an argument so this case I already picked that up from uh from web I will uh put this one is for product I think but it it doesn't matter really so I just want to show you what you can do so I can put like this let me just paste it everywhere and then I will just change the values in Brackets so for calendar is uh 1281 98 like this for geography is 1 27757 and for product are those values so what will happen now we will have nice icons representing our groups so you are not limited just to to to uh static values you can play around and introduce some additional things here so this one is for geography this one is for products and this one is for calendar go and uh uh be creative be uh uh let your imagination do the job and yeah find uh maybe some uh even better things to to to use here and one one fun trick that I like around that is I used to use the unicar function but if you actually put in Brackets you can copy the the the icon emoji and put it directly in the dox code as well rather than having to use the unicar function which I absolutely love oh okay that's that's good to know I do so question do you do you have the the web page with the unicar symbols or anything sitting next to you at all uh let me just or just if you if you want to use the windows emoji keyboard I can just show you that you can just put a random symbol in there like the the Windows key and yeah yeah so let's go back to the measure that you were just showing where you were typing in the uni unicar um if you can just open up the measure code because you have to insert it somewhere so uh yeah let's let's go back to the the code and then open that up and then um just replace the first line with anything so just get rid of that put put two quotes in there two text quotes and then just stick the stick the Emoji right any Emoji right in the middle there oops let's do this one sure there you go okay let me let me just put also here Windows key and Windows key period apparently works for it ah nice okay okay yeah yeah so the nice thing about that is you don't have to like what is 12542 it just shows up um as that and it's it's easier to read um some people are like I don't want to have emojis in my actual code at which case maybe you could declare a variable with a friendly name but I I found that as a really nice little like oh that's an easy upgrade yeah yeah I didn't know that it would work yeah that's good to know it's good to know that's good to know yeah awesome uh yeah so as I said no considerations and limitations we will come back later to this uh some more things that I wanted to show you and uh that are important in my opinion if you consider working with uh uh with field par parameters one of the things that I heard many times uh from developers is okay field parameters are nice but if I want to uh use like selected value to dynamically display somewhere in the title or somewhere in the in the report page uh I can't use selected value out of the box as with regular slicer so if I go back here you will notice that all of my pages look exactly the same it's just different things that uh I'm doing with with field param M uh uh on them so let's say that I have a regular uh a regular uh uh slicer here let me just pull this brand name and let's say that I want to this is not a slicer now it is let's say that I want to uh dynamically show the selection of the slicer somewhere on my report page it's very very easy to do uh uh with regular tables and with regular TXS so you just say selected value selected value from my brand name okay and let's put this just in a card visual so you see how it works selected the cell value once I click here you see that value is dynamically changing now this is not possible with uh with field parameters if you try to use selected value it won't work but there is a workaround for this and I took this code I think it's from Marco and Alberto on SQL bi so I just wanted to share this code with you to show you how you can basically create uh how you can grab selected value from field parameters slicer so let me just move this here and you should just be switching it over to Max right uh it's a little more complicated uh I'm not sure if if it's going to work with Max but uh with this with this approach we work for sure so if I go which one is Parm this was three T I think so if I select ah no no no no sorry so if I create a new slicer and I put this three test here and here I put three test so the idea is what is selected in this slicer should be displayed as a selected value so I will just because it's verbos code I'll just uh uh copy and paste and then uh quickly explain although you can go and grab the code itself so just I need to switch the name of the it's not parameter it's param Tre test right so let me copy this everywhere just to switch it okay perimeter retest and this one let me just I think it will ask me to do again so Tre this test this is fields and this one is also this okay and this should work now names are not the best because uh but yeah never never mind it just wanted to show you so in this case I'll put this one selected value from uh field parameter is country in this case brand so you can uh I'm not sure with Max's approach maybe maybe it's worth trying but this one will for sure return the value selected value from the field parameters slicer so out of the box with classic selected value will not work but this one with the with this Dex code uh uh should do the should do the job so it's possible to uh extract selected value and one more and the well and what your approach does as well is it also um if you clear out brand what what happens with the um the output ah it crashes okay something needs to be selected ah yeah okay so there is a table so curiously just um if if you want to move on it's f i I just I I had a I thought on on a on a potential alternative to the measure if you wanted to test it otherwise we could go to conditional format and up to you yeah we can test it yeah why not yeah yeah yeah let's go to the measure that's in the selected value from FP is this one um just at the very bottom like just don't worry about the variable just simply if has one value um for whatever that column is um and you'll need a yeah there you go parentheses basically you're you're pointing to the the column that has the the country or other option I forget the the exact column name it's I think it's it's parametry as I think that's your your uh that's the column with the labels right the paramet Tre as um yeah that one uh yep exactly um then simply comma Max parametry as which will'll just grab the single value from it and then simply close the parentheses provide no alternative results this one like this and exact and we get rid don't need it yeah exactly let's see if that works and interesting what's the error message say what is if you click C details ah so okay so the the has one value similarly has a selected value max if you just use max in isolation Max will always grab either of those but the problem is Max also will grab the first or last even without a selection so has one value similar does that so if you if you did one small edit if you change that to count rows is equals one from that table that would actually that should actually I think uh resolve it so as one value becomes count rows um so yeah do do has do hasn VI replace that that logic to count rows equals one not at the end like um yeah we want to get rid of the column so param treas the column name needs to be removed one okay yep so we want param treas we just didn't want the column in there uh like this yeah no no no so count rows on the parametry as table uh T is param treas paramet treas yep close parentheses equals one par equals one comma let's try that last last that should that should get it there we go and now clear out country Tada ah nice nice okay cool that's more user I think that might be one of the uh yeah one alternative way to um to do a logic to check is there a value selected otherwise don't return anything because you don't necessarily want it to return country your brand which Min and Max would return on its own but that's an alternative way to do that because you can't uh you can't use has value you can't use selected you get that weird error that says this table is part of a composite key and it you know feat limitation that doesn't help at all but this one this one is is better I think uh because there is no error at all M okay cool cool yeah yeah cool yeah nice nice okay then uh we can now move on to conditional formatting another thing that that I'm hearing it's not possible to uh do with the with the field parameters and yeah with regular this is just a regular measure no field parameters at all so if I click on this small arrow and this is option for conditional uh formatting uh in this case let's create a new field parameters h a new field parameter uh modeling fields and and this one I'll call parameter uh conditional and I'll put for example sales amount and let's say I don't have a measure for I'll put sales quantity as as an implicit measure doesn't matter okay and create a new one so here in this case instead of sales amount I'll put my parameter conditional as values and if I click now on small Arrow H there is no uh there is no option for conditional formatting so let's just move it okay and how you can do this uh if you go to sell elements and then for sales amount I can turn on uh I don't know datab bars or font color or whatever so you can do the same thing as with uh with regular uh with regular conditional formatting but in this case it's not available out of the box from this small Arrow you need to go to cell elements and then let's say uh for background color all the same uh uh set of properties is available so instead of gradient you maybe want to apply conditional formating based on rules so everything works exactly the same it's just one step more it's it's a little bit hidden I would say here in cell elements but you you can still achieve conditional formatting uh with field parameters it's doable it's doable that's most important thing and one last thing I wanted to show you and I have to admit this is my favorite uh as I said all these things were related more or less were related to data visualization tricks and techniques how to how to leverage field parameters but uh as we said you can use also field parameters for dat to solving some data modeling uh challenges and this one that I'm going to show you now I implemented in real life so it it was real customer with real data uh of course uh here in this example this is just a uh uh dummy numbers and dum dummy information but you can think that literally data was data was looking the same as here in terms of of the structure and the data model so what I have here I have different products AB bcde e and I have uh two different types of Premium it was insurance company that's why we are talking about premium so we have earned premium and written premium and all of these values are displayed in three different currencies local currency USD and Euro so what was the business requirement that user uh should be able to select so they have two slicers one slicer is currency the other slicer is uh premium type and if they select Euro they should be able to see only earned premium and written premium in Euro all the others should not be part of the re of this Visual and uh uh the other way around if they select just a written premium type and not not not any currency then uh they should be able to see all written premiums in all currencies so this is completely like dynamically switching what is displayed in the in the in the uh in this visual here in this table visual if you create a field parameters simple field parameters and put all of these uh values in field parameter this is not going to work or this is going to work just for this single visual but on this report page imagine they have like 10 different visuals and those slicers those currency and uh uh premium type slicers should affect all the other visuals not just the one with a table so what I did here basically I uh apply some changes to a data model so imagine that they have a table with uh which is called uh currency and we have three currencies Euro USD and local so this one is currency imagine that this is dimension currency in their in their uh uh actual actual data model and and another one which is called premium type of course it contains additional columns but for this example it doesn't really matter so there are two premium types earned and written and let's call this one premium type okay I'll load this to my data mod so I have those two let's say Dimension tables and this is my fact table this one here so essentially what I'm going to do uh let me just put those three tables so it's not there is no clut okay whatever so premium type and currency and my where is this where is it ah premium okay so what's the idea now uh I will create basically a new table with uh field parameters and use this one instead of the main table here here this one premium so again I will go to modeling new parameter fields and in this case let's call this one param uh premium so I will grab everything from my premium table all possible values except product we don't need that so all the values here but in this case I don't want slicer on my report page because then this slicer will only affect one visual remember we want slicer that affect everything on the report page so I will uncheck this box so I'll just create a table uh with field parameters and let's go back to our model view and I'll put this param premium here okay and uh I'll extend this table like I did in previous case with those nice em uh uh emojis so in this case I need to add two columns one will be currency and the other will be premium type so in this case for currency this is Euro and for premium type this is earned okay so this is earned premium in Euro that's why I'm using Euro and earned let me just copy paste here this one is uh local and earned and this one is USD earned so we are now doing some data modeling stuff this one will be Euro not point but comma and written oops oh no I need to start from scratch I pressed something wrong so Euro and earned okay let's this paste here and paste here this one is local this one is USD and here we have Euro and written and we have those two here is local and written and here is USD and written so we covered all of those attributes so you see we have those value four and value five let me just quickly rename them so value four is essentially currency and value five is premium type the last and most important step is to create relationships between those two tables so I will create a relationship between current currency and currency and I'll create a relationship between premium type and premium type so regular one to many relationships nothing special but what happens now let me introduce two two slicers so I'm not using slicer from uh field parameter table I'm using my regular Dimension tables from the actual data model so here I have currency let's just do a copy paste and here I'll put my premium type so those those are the options and here I will go and remove all static values and grab my parameter premium here as a column now it looks exactly the same like the original one but look what happens if I click on Euro bam so this relationship basically will serve as has a filter so whenever user clicks here on on Euro the model will be filtered through the relationship so I selected Euro this filter propagates through the relationship and removes all the columns that are not uh that are not Euro if I select Euro and USD both of them are here if I select only earned then you see earned premium in Euro earned premium in USD so you can play around I mean this this is fully fully flat fible and customizable solution so in this case with two uh two slicers in case you have more than two Dimensions uh and you want to enable yeah more fun with with this Dynamic switching it's also possible but yeah it's everything is done through the data model so uh all all all uh user selections will propagate through relationships and basically displayed uh Define what is displayed in in this table so yeah and going back yeah this one is really really really cool uh yeah I mean we all have field parameters already and are super excited I'm sure so but there are some considerations and limitations as we said it's almost like a regular calculated table but there are some limitations uh that we need to be aware of uh uh let's say the first question one that I'm getting asked often is about performance uh and I will immediately tell you that there is no performance difference between using field parameters and regular column because once the powerbi retrieves the list of the columns from from uh this field parameters table it will then just substitute them and use regular columns in in the in the physical query um next and this is I would say say uh important consideration if you're using analyzing EX ccel feature at this point in time uh uh it doesn't work because Excel uses MDX to populate pivot tables it's not even aware of field parameters uh I would say this is currently and probably the biggest limitation of of the field parameters feature and in addition you can't use field parameters as linked fields for drill through or tool tip page uh there is a workaround uh to use individ ual fields that are referenced within the FI field parameter instead for example if you have a field parameter that includes uh countries and product Fields uh you can link these two fields to your drill through or tool tip page so then visuals with field parameters uh can launch your drill through or tool tip page and with that being said yeah I think that that's everything that I wanted to show you if there are any questions or comments I'm looking forward to to hearing them yeah we we had one earlier that may or may not have been answered mat was just wanting to know kind of like why wa we have to add it in power Bay desktop and it it boils down to you have to materialize and an import and cache table in the model with adding those um which is also kind of unfortunately uh one reason that like if if you have a golden data set you actually uh you need to create a composite model to add a field parameter is as well you know so there and there's those whole conversations around like upstream and downstream and where where to put those but that's actually a best practice that I even recommend for a lot of people if you have a primary data set that is servicing lots of different reports uh report scoped items such as like custom measures for titles uh field parameters usually if you have composite modeling as an option in the service for your tenant I do recommend that you add them to the uh the thin report and yes you are adding a local model but you're not technically adding new data you're just adding a couple of cache tables that is referencing the live data um but it it it lets you push it Downstream but all of that has to be done in powerb desktop today uh just because it U it requires adding some type of physical table into the model and making a model edit which there is some preview features in the service to do model editing but technically if you can mention uh remind me I don't believe you can add field parameters in the service you can only do like power tables not today yes yeah as of today yeah maybe maybe maybe that's one of the reasons why it's still in preview who knows uh as I said at the beginning I'm curious to hear from Jay uh what are the current limitations that Microsoft considers uh uh as a blocker for GA I I'm really curious to hear because so far I really implemented field parameters in many reports and didn't have any single problem with them so it's just maybe some feature parity or or the things you mentioned previously so maybe maybe it's something on that and Mark was uh also just commenting on using relationships with field parameter tables that I might have to dig up the the video that I did from about a year ago but there there I I found a lot of fun ways similar to what you've done to leverage the order column because that's essentially kind of a key that you could use like zero1 two can key to other stuff so like if I have a report page that always needs to show of actuals budget or variance just it's always three metrics I actually created um three different field parameter tables that are offset from each other so if if from that first table if I select actual it keys to another table which will mean that the two tool tips will be budget and forecast but if I select budget the two tool tips will be actual and variant so it always will make sure that at any time between these three connected field parameter tables one of the values will be in the actual visual you know representing the bar or the dot and the other two will always be a rotating comparison that will show in the tool tips and it they always just uh basically circulate around which you'll never end up duplicating in any measure both visually and in the tool tip at the same time um and all sorts of other stuff but key keying your parameter tables to other disconnected tables to have them sync together there are many different possibilities so I highly encourage people to kind of explore that as a as an option sure that's also fantastic use case that's also fantastic use case and as I mentioned during the session uh it's literally it literally opens a whole new world of possibilities this was just to give you a hint and the things you mentioned also can provide additional hints I know many other uh people from our community blogged about how to use field parameters to solve this or that it's really it's really it's really amazing how many things you can uh think about with to solve with field parameters now um do you remember that the video from bos like a year and a half ago where he he automatically scaled the the x-axis by date uh using field parameters yes I I really wish I could have come up with that one that so that that uses the rank function to always return one value on the parameter table but he essentially had a had a date range um on on the side where you know you could pick between like January 1st and the 18th and I think if it was more than a month or more than three months it showed the year uh it automatically showed the year on the x-axis if you went to Less Than 3 months it went from year to month and then if you were less than 3 days then it automatically changed the date so it had an autoscaling AIS based off of your date range and essentially I think he he did there was a measure that he used uh and he applied a top n to the parameter table um and that automatically selects one value at a time based off of some fancy logic I'll I'll see before I finish the stream if I can actually quickly find that but that was one of those like that is one of the most clever use cases that I've seen for parameters and it's just I really wish I had thought of it but I was very impressed with that one as a very clever trick for field parameters yeah yeah yeah I remember that one I remember that one yeah um and I will say for anybody else if they have any questions uh feel free to drop them in there as they're wrapping up I'm just quickly seeing autoscaling axis and then B let's see if I see if I can actually find that because that that was a great video and I just love to give a few people a few more resources but uh otherwise what would you recommend to check out um for people who maybe wanting to explore more about field parameter options or anything else that I can look up Kane Kane Snider did some great things with the with field parameters as far as I remember uh also recently Matt Matt Allington uh had a great blog uh uh also some very very edge use case but uh annoying thing when you can't delete the blank from when you select when you have two parameters two slicers that are connected and you uh uh deselect the the previous value stays selected so with whe parameters there is a solution to this I I can't uh tell you now how it's a little more uh uh uh work let's say but uh in the end for fi you can use field parameters also for for solving this challenge so those two that this came up first to my mind uh yeah yeah I mean there Dro those two there yeah MH so at least yeah couldn't couldn't find the exact B when I got the other two in there and there's at least a link to B's channel that somewhere in there about it was about a year and a half ago I think on October when he came out with that it was about yeah long so long time ago yeah yeah long time ago I think field parameters appeared in May so he did it maybe a few months after that in May 2022 so yes because they're almost yeah yeah yep beautiful um but then otherwise I don't see any other comments just uh a few accolades uh just passing this on from Jack here so he wanted to just shout out with a thank you and say that was an excellent presentation but this was a anything reporting is always one of my favorite talk to go into and uh I definitely learned a few other little tips and tricks it's such a versatile tool the one thing I actually might just be good to uh just to briefly mention in here for um anyone tuning in and to give Mark apparently a chance to ask his final question c groups and field parameters we talked about a little bit I don't think we ever got specifically into the the either or it's a very much it depends scenario and the only overlap that they would have is between switching between measures with a uh with a field parameter or switching between measures in a calculation group and I I'd say the easiest way that I could describe it and i' love to hear your thoughts on this as well is it depends on the scope of the where you want to apply the logic if you have like a Matrix table or something else where you really just have like one measure that needs to be switched or the logic needs to change for that field parameters are super easy to do that with calculation groups without a lot of very manual edits in the code the smallest scope that they can apply to is at the visual level at one visual at a time but that that goes on top of all the measures that are in there not just one or it's the page or report level so if you need to be hyperfocused onto one measure field parameters are great for that and swap between a b and c if you need a broadness scope to one visual at a time for repeatable logic or the page of the report that's when i' to say cons consider a calculation group instead yeah yeah I I tend to agree with that so you can do more or less uh uh uh all tasks or all tasks of the this type with both of them but it's just a question which one is more convenient to use for the specific use case I agree with you field parameters if you have just one measure uh uh uh more straightforward solution yeah it's it's kind of a nice analogy is like do you do you have a very precise like Jeweler's hammer or are you using you know a larger Sledgehammer for for what you're applying with and like the it's a very precise um application with a field parameter and then the scope just increases when you get to calculation groups um to Mark's question that he finally typed up I'll pass this off to you Nicholas so do you think there's a way to limit the start and end dates on a date slicer based off of a field parameter limit the start 10 dates on a date slicer so think about it this way you you have you have a date range slicer I I I'm at least picturing the question he has in his head and your calendar table is January 1st 2019 to January or December 31st 2024 is there any way to use a field field parameter to have that dynamically change in terms of what it's stared in might be not that time aware of on I think it's more that's a that's a measure I would just put a measure in there um for for that because parameters is more about like yeah there's no row context really for a parameter it's not applying a filter It's The End Result you can't really yeah filter it yeah but if you have a measure that simply returns a binary zero or one you can apply a visual level filter to a measure or sorry to a slicer and slicer you just need to have a Dax measure that basically determines your start and end and you have like you know if the calendar date is you know less than or equal to the Min sorry greater than or equal to Min less than or equal to Max one else zero if you filter that measure to equals one on that then it automatically will filter your range dynamically and I've done that before in um at a few client scenarios where it it basically is dynamically filtering the date column based off of a measure that you provided the logic into yeah but I think on you then you don't need field parameters it can be done without them exactly uh Alejandra had another question have you have you managed to use field parameters or calculation groups in the reference label section of the new card visuals uh I haven't tried it myself honestly a so the calculation group itself is something that's done on top of a base measure so you could put a base measure in the um the reference label section and then if you applied a a calculation group at the visual level to the card yes you could use a calculation group in there I'm I'm not positive but I'm 60 70% certain if you try to put a field parameter directly in some of those new um card visuals I don't think it takes it it doesn't accept it as a uh as a calculation I've not tried it myself but I I know Cal GRS for sure you can use um I'm not positive at least with the um field parameters as a measure if that can be dragged into those Wells just because it's still a preview feature visual um okay I think that was about it for the questions I don't see any others so Nica I want to again thank you for taking your time out of your evening coming back on we got through this with zero issues and perfect quality so that was great thanks for having me thanks for having me yeah thanks for giving me another chance absolutely and I will uh see you once or twice next month right will you be in uh renand or uh no un fortunately not so I I'm just coming to Vegas in in March it would be too too much traveling all around the world but we will see each other in Vegas yeah absolutely then I will see you in Vegas and enjoy the rest of your evening yeah enjoy Japan and uh yeah enjoy the rest of your day and thanks everyone for joining all right cheers thank you so much for watching please consider hitting that like And subscribe button and if you want to help support this channel take a look at our Channel memberships or our merchandise store for cool swag and last but not least please consider sharing this video on your social media platform of choice to help our Channel grow so until next time

Channel: Havens Consulting

Views: 2,257

Keywords: Power BI, PowerBI, PBI, DAX, Data Modeling, Visualizations, Tips & Tricks, Power Platform, Power Query, Power BI for Beginners, Power BI Training, Power BI Desktop, Power BI Best Practices, Power BI Relationships, Power BI Dashboard, Power BI Tutorial, Power Query Excel, Power BI Versus Excel, Power Query Tutorial, Power Query Functions, Power Query Parameters, Power Query Editor, Power BI Service

Id: LoiSilbl0lg

Length: 75min 4sec (4504 seconds)

Published: Wed Feb 21 2024