topic DAX dynamic dimension value (calculated column value) based on measure defined by slicer in DAX Commands and Tips

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

iz08 — Fri, 01 Apr 2022 16:01:52 GMT

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

I have uploaded my pbix here.

Thanks for your ideas.

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

johnt75 — Fri, 01 Apr 2022 16:25:47 GMT

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

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

iz08 — Fri, 01 Apr 2022 16:42:07 GMT

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

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

AlexaderMilland — Thu, 11 May 2023 07:44:10 GMT

Did you ever resolve this?

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

NoSpaces — Thu, 11 May 2023 07:58:21 GMT

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.

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

AlexaderMilland — Thu, 11 May 2023 09:22:23 GMT

Yeah thinking I need to use a hardcoded calculated column unfortunately