dynamicpowerbidaxpowerbi-desktopdimension

Dynamic measure that responds to dynamic dimension


I'll try to describe this scenario without introducing too much irrelevant info, but keeping it simple.

Using the newish Field Parameter feature in PowerBI, I created a Parameter called _Dimensions and another one called _Measures, selecting common columns in the former and common measures in the latter.

I then build a bar chart with [_Dimension Fields] for X-Axis, [_Measure Fields] for Y-axis, and a single-select slicer for each. Now when user selects a measure and a column, it draws a bar chart of their selected measure, sliced by their selected dimension.

What I'd like to do is actually make this a Pareto chart, which would entail putting in a second measure on Y-axis, but rather than having a pareto counterpart to every possible measure a user may select, I'd like to create a single measure that calculates running percent of total of [selected measure] along [selected dimension].

I was hopeful I could call the [_Dimension Fields] column that PowerBI created with its special properties from DAX, but that doesn't seem to treat them any different than any other column. I also tried NAMEOF, but that just returns a string. I was hoping it would act like INDIRECT does in Excel, treating the string as a reference, but alas.

Does the above problem statement make sense? Can anyone describe an elegant design approach to do this dynamically that does not involve just writing a version of every possible measure a user could select and then use a switch?

imagining the combo chart to look like this (pareto measure in line chart part)

enter image description here

edit: secondary question, but equally important to the end goal of a fully functional dynamic pareto: when user selects measure, I want the selected dimension to always be sorted desc by selected measure. This is how you do a pareto analysis, but PBI does not default to sort descending always, and each time you change the dimension (via slicer click) the chart resets sorting. Any way to ensure that the sort order is fixed correctly?


Solution

  • Calculation groups are the way to go and Tabular Editor is used to create these.