cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
navpienaar Frequent Visitor
Frequent 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?

 

 

8 REPLIES 8
Microsoft v-yulgu-msft
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.
navpienaar Frequent Visitor
Frequent 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 )')]

 

 

j_minton Frequent Visitor
Frequent Visitor

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?

j_minton Frequent Visitor
Frequent Visitor

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.

CerbPBI Frequent Visitor
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.

AIW Frequent Visitor
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. 

AIW Frequent Visitor
Frequent Visitor

Re: Dynamic Dimension

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

vimala Frequent Visitor
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

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (1,233)