Dynamic Dimension

[navpienaar]

I want to create a Dynamic Dimension by creating a “global variable” getting a dimension from an island table (Dimension = Customer, Item, Salesperson, Region …)

DAX Measure or Column:      vDynamic_Dimension = FIRSTNONBLANK('Dimension'[Dimension],1)

Is there any (simple) way to use this Measure (Global Variable) to “fill in” the dimension on the X-Axis of a Power Bi Table?

Is there any way/syntax that I can use vDynamic_Dimension a dimension in a table and it will change according to the field selected in the Dimension Slicer?

[Anonymous]

Hi everyone,

I'm also very interested in this topic.  I am coming from a tableau background and have developed reports where the user can define 2 separate dimensions that he/she would like to break the data out by. 

I'm in transportation, so in dimension 1 the user may select something like 'Origin' and in dimension 2 the user may select 'Carrier'.  They could then see whatever the measure is for the given chart broken out as follows:

Location A Carrier A Key metric

Location A Carrier B Key Metric

...

...

...

Location B Carrier A Key Metric

Location B Carrier B Key Metric

...

etc.

For each dimension I have 9 options.  The book mark solution doesn't seem viable, given there would be 72 (9*8 b/c you wouldn't have the same dimension twice in one view) views that I would have to build.  My data set is around 1M records.  Is the cross join table solution even viable?  How much do you think having a bridge table with 9M records is going to impact performance?  Thats only for one dynamic dimension, if I wanted both dynamic dimensions I would presumably have to create 2 bridge tables now totaling an extra 18M records in my data model right?

I think my tableau users are going to be sad if this is the best solution I can give.

[Anonymous]

There is another solution for implementing dynamic dimension slicers optimized for performance.

https://www.godataviz.com/post/switching-dimensions-based-on-slicer-selection-without-bi-directional...

Here you can add new dimensions in the DAX code quite easily and it offers a huge degree of flexibility.

Let me know if you need more informartion

[Anonymous]

Nice solution

[Anonymous]

It also easy to set up in Tibco Spotfire. I'm testing out Power BI and surprised there isn't a simple way to facilitate this. The solutions I've found all propose using a bridge table. I have dozens of dimensions and millions of rows. Adding several million rows to my data model doesn't seem like an efficient work-around. Bookmarking is clunky and doesn't work intuitively for a solution. The "Go to the next level in hierarchy button" interates through all the dimensions, just like I need, but there is no way to interact with the feature other than that single icon. Also, I'm not sure how the end users will know how to find the hierarchy level they need. I was able to follow a tutorial about being able to select measures dynamically and it works great. Unfortunately, the same code does not work for regular dimensions. 

[j_minton]

As @v-yulgu-msft points out, this is useful: http://www.leanx.eu/tutorials/dynamic-dimensions-in-power-bi

However, while the author adequately describes the need to create a join table and the values it must include (a unique cobination for the unique identifier plus the dimension name), there are no instructions on how to create one. How do I create a join table in the query editor? 

Also, as @navpienaar points out, architecting this functionality is far simpler in Qlik and I wuold like to add that it is also easier in Tableau.

[CerbPBI]

Its also easier in Business Objects.

All we do we is write an if statament

if selected value="Employee" then [Emplyee Dimension] 

elseif elected value="Department" then [Department Dimension] etc

I'm also looking to achieve the same in Power BI without creating a relatinship to an unpivot table. Unpivoting table with 8-9 dimension columns simply blows up the rows. 75K dim table turns into 750K if 10 columns were to be used in dynamic selection.

[v-yulgu-msft]

Hi @navpienaar,

You want to dynamically change dimensions displayed on X-axis depend on slicer selection, right?

If so, here are two samples for your reference:

Dynamic change in X Axis

Dynamic dimensions in Power BI

Best regards,

Yuliana Gu

[Anonymous]

@v-yulgu-msft Is there a feature page for this functionality that we can vote on?

[vimala]

i am also looking for the same, not doing Unpivot table option. As that will blow up the datavolume even for a qtr ...then how do we do YOY analysis... unimaginable.

If anyone knows please share your tips

[j_minton]

The walk through here is helpful, as @v-yulgu-msft points out: http://www.leanx.eu/tutorials/dynamic-dimensions-in-power-bi

However, the author mentions that creating a join table is a critical step but fails to instruct readers how to make one. The logic is described adequately: a unique combination for each unique ID + dimension value. But how do I generate that table in the query editor?

[navpienaar]

Hi Yuliana 

Thank you for your feedback.

I saw the examples you mentioned. They are complex and require you to rework the logic every time you add new dimensions to the dimension table. There must be an easier way.

In Qlik I a can link a variable to the standalone dimension table that might hold sixty possible dimensions. The variable will return the dimension value selected, and that value will pop on the X-axis if I use the under mention syntax.

I am aware of the fact that this is Power BI, and that Power BI does not have global variables, but are looking for a simple way to make a DAX value “pop” on the X-axis of a chart or table.

Variable (Qlik):

           vDynamic_Dimension =MaxString(Dimension)

Syntax for Dynamic Dimension (Qlik):

           =[$(='$(vDynamic_Dimension )')]