Forum Discussion
DAX dynamic dimension value (calculated column value) based on measure defined by slicer
Hello DAX fellows
I am trying to create a dimension (table column) that changes its value based on selected slicer value.
The use case is: Select an X value and for each row of table calculate Value > Selected X.
E.g. to classify stores into two categories: where Profitability > 10% and where Profitability <= 10%. I need to be able to set this Profitability threshold from a slicer.
The end result should be a table:
| Value > Selected X | Nr of Values |
| True | nr values > X |
| False | nr values <= X |
- I created a test table of Values from 1 to 100 and column [Value > Selected X].
Values = GENERATESERIES(1,100,1)
Value > Selected X = 'Values'[Value] > [Selected X]
- I created a table [X Values] with values of X and the measure [Selected X]
X Values = DATATABLE("X", INTEGER, {{0},{25},{50},{100}})
Selected X = if(HASONEVALUE('X Values'[X]), VALUES('X Values'[X]), 0)
- In the Power BI page, I can select X from a slicer and see how [Selected X] value changes.
- However, the column value [Value > Selected X] does not change, and default value of [Selected X] is used.
I have read in this forum that the table is not dynamically calculated, and therefore the page context does not influence the table's calculated column values.
I would like to understand how to achieve such dynamic filter that allows to split my population of values in two categories [Value > Selected X]: True and False. And then I would like to use measures together with these dimensions (e.g. [Nr of Values]).
Thanks for your ideas.
5 Replies
You would need to create a measure like
Above Threshold = IF( SELECTEDVALUE('Table'[Value]) > SELECTEDVALUE( 'Threshold Table'[Threshold]),1, 0)You can then use that as a visual filter to show only things where [Above Threshold] = 1. You can also use it in other calculations like
Sum above threshold = SUMX( FILTER( 'Table', [Above Threshold] = 1), 'Table'[Amount])Thanks, but that's not exactly what I need.
I need to split my population into two categories (dimension values true and false) and be able to compare and filter these 2 categories by different KPIs. That is why I was thinking about the dimension.
I would be able to create measures that use the slicer, but I need to see the underlying data (e.g. which stores have profitability less than the dynamic value from the slicer).
Did you ever resolve this?
Hi, I'm the original poster.
I didn't win this one, because DAX doesn't support such functionality.
I worked around that one by using either a numeric slicer or a hardcoded value in the calculated column.
Don't remember the exact solution because the project had been finished a year ago.
Yeah thinking I need to use a hardcoded calculated column unfortunately
