cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Regular Visitor

Dynamic Dimension

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?

 

 

9 REPLIES 9
Highlighted
Microsoft
Microsoft

Re: Dynamic Dimension

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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Regular Visitor

Re: Dynamic Dimension

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 )')]

 

 

Highlighted
New Member

Re: Dynamic Dimension

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?

Highlighted
New Member

Re: Dynamic Dimension

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.

Highlighted
Frequent Visitor

Re: Dynamic Dimension

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.

Highlighted
Frequent Visitor

Re: Dynamic Dimension

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. 

Highlighted
Frequent Visitor

Re: Dynamic Dimension

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

Highlighted
Frequent Visitor

Re: Dynamic Dimension

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

Highlighted
Frequent Visitor

Re: Dynamic Dimension

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.

Helpful resources

Announcements
Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors