Dynamic Column Headers (with Field Parameters) in Power BI!

hey data fans Reed here today I'm excited to walk you through an update and a bit of an enhancement of a video that I did a few years ago so a few years ago I had done done a video from a client request that was asking to essentially create some level of dynamic column headers now there was some ways using some relationships and some other items and a bit of Dax to have them update automatically and have the title name change during model refreshes but there wasn't really a way to drive column changes via slicer selection C in real time when using a report now I got another client scenario this year where I had a similar request if you can see in front of you here we have three columns that are shown in a visual and the actual column names in The Matrix table is now changing dynamically with the release of field parameters last month I'm now able to do something like this and actually have a bit of clever connections between two tables a selection for a window size in this case and a way to create some quote unquote Dynamic column headers utilizing the field paramet themselves so let's go ahead and hop into powerbi and see how this was [Music] done so the scenario that at least the client had in this case was something where they wanted to adjust a dynamic window size they were going to have a slicer selection for AG invoices I just have some generic sales data where I want to use this slicer here in the upper right to determine the window size so with a selection here of I am given in this Matrix table below sales that is from today through the prior current month and this second one over here is from the prior 1 to two months and then prior 2 to three so three separate windows that don't overlap but they all show buckets related to my selection I change the window size to two this will now change it to Prior two 2 to 4 and 4 to six respectively and then prior 3 3 to 6 6 to9 but this is dynamically changed in the actual column header here at the top I'm not overlaying this with text boxes or anything else so the visual itself is reprocessing with different column header names now if I select this here the visual and take us over to the field parameter that I'm using what I've actually done is I have three instances of this measure being referenced in here so my window one selection at the top and I will break this all down for you after we look through this but this window one here when the number one is selected for my window range will return measure window 1 measure window 2 and measure window 3 but with a special name to call it as you've seen in the visual so prior one prior 1 to two now I also have another selection here where this is going to return those same three measures but with three names that represent the wider Windows the window 3 selection returns those three same measures with the appropriate names again going with the wider Windows now what I have in here are a couple of special keys and another table so I have two tables that are connected together if I come over to the model view I actually have my parameter sales table window here at the top that I've defined that lets me select my window size and I have a key between both of these so let's actually look at what these tables look like so my Dynamic columns here what I have is I have those six rows to represent the three windows for each of those periods were the selection of window one selection of window two for Rows 3 to six and then 6 to9 are essentially that third largest window and I have a special key that is either key to the first three rows being zero the second three rows being one and the third three rows being two now this has a key to my sales window table where that column that you see in the slicer that month window size which is 1 2 or three there's a key representing 01 or two that is in a relationship with this lower table where these are keyed together so there's the window key here to this other table between those two and with this existing that means that this slicer here drives always a selection of just three of those parameter rows so when I select one it Returns the first three rows of the same measures but those first three rows get the column name or row name basically of these three that you see here so the measure changes the logic in the back end to account for the window sizes but also the column names change because the parer has been repeated and then keyed between these two tables so one other really nice benefit of this is the fact that because the measure is technically the same anytime I was going to apply like a data bar to any of these or anything else it considers it the same measure as well so it's recognizing the measure as essentially loading with just different values so it doesn't reset the visual if I actually was to apply a data bar to any one of these window sizes which is one added benefit now the measures themselves are kind of irrelevant in terms of what the logic is it's more of a focus on the column headers but I do essentially have some logic in here that I can quickly walk through so I basically just have the window sizes that are fetching whatever that selection is and I want it to be keyed to my offset so the start offset is going to be the window size times minus one so if I select one that's going to be a minus one so that's how many months back I wanted to go with the end off set of zero and I just specify via the variables that I have here the window sizes so I have a 2x1 to go back because I want it to start and end and not overlap the first window and I have some notes there to represent uh those logic changes here as well and then also making sure that I change my less than or equal to to less than so it does not overlap window one and correspondingly for window 3 similarly it's now going back 3x rather than two and the the end of this window range will also make sure that it ends at the same place that window two does but also not overlap it with a less than symbol so again the point of this video is less about what the measures are more about the dynamic column headers themselves but I felt a need to also show those and of course you can always download this file from my blog page and dissect and rip it apart as much as you'd like but I do like the output of this it let me for the first time kind of actually create a visual with Dynamic column names which is something that hasn't been feasible or very easy to do that much outside of exploring custom visuals like info River or other options like that but I do like how it works it is exactly what the client wanted and it was giving the intended output for this um the one thing you have to do is just when you are creating the field parameter you essentially just need to ensure that you've copied it down and then appropriately keyed that now it is kind of important if you want these in a particular order that you keep the sort order in a correct U logical order that will go down this way and separately create a key column between the two tables that is why I have a specific window key on my select ction for the window size and on the actual Dynamic column table there is just the order of which these are supposed to be displayed but then separately there's that window key that goes back to it um otherwise if I tried to change this to 00111 and so on then these would start to display alphabetically uh because they have ties with the ranking in terms of how how they're supposed to sort so that window key does ensure that the initial order that you would like them to be displayed left and right will be maintained but as always hopefully this is a video you found useful I'd love to hear other use cases of how you might incorporate some Dynamic columns into some of the stuff that you've done as well as always check out some of my related content here in the upper left don't forget to like comment subscribe to help my channel grow and then otherwise I will see you all in my next video

Channel: Havens Consulting

Views: 4,313

Keywords: Power BI, PowerBI, PBI, DAX, Data Modeling, Visualizations, Tips & Tricks, PowerPivot, Power Pivot, 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 BI DAX, Power Query Excel, Power BI Versus Excel, Power Query Tutorial, Power Query Functions, Power Query Parameters, Power Query Editor, Drill Through, Power BI Service, Fabric, Semantic Model

Id: BLNqU2jJ7u0

Length: 7min 44sec (464 seconds)

Published: Tue Mar 12 2024