DAX dynamic dimension value (calculated column value) based on measure defined by slicer

[iz08]

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

1. 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]).

I have uploaded my pbix here.

Thanks for your ideas.

[AlexaderMilland]

Did you ever resolve this?

[NoSpaces]

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.

[AlexaderMilland]

Yeah thinking I need to use a hardcoded calculated column unfortunately 

[johnt75]

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

[iz08]

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).