topic Re: DAX table with dynamic values based on measures in DAX Commands and Tips

DAX table with dynamic values based on measures

Standef — Thu, 08 Feb 2024 16:25:03 GMT

Hello everyone,

I am trying to have a table written in dax, with MIN and MAX values of a measure.

I have a measure that varies based on several slicers; I woul dlike to have a table that automatically creates 4 clusters based on this measure.

I have built a table with 4 columns (id, Segment, Min, Max) like so :

table_cluster =

var boundary1 = calculate(MAXX(ALL('Store-Country Map'[STORE_ID]),[MY_MEASURE]))

var boundary2 = (bounder1 / 4 ) *3

var boundary3 = (bounder1 / 4 ) *2

var boundary4 = bounder1 / 4

var boundary5 = calculate(MINX(ALL('Store-Country Map'[STORE_ID]),[MY_MEASURE]))

return

{

(1,"Major", boundary2, boundary1),

(2,"important", boundary3, boundary2),

(3,"mid", boundary4, boundary3),

(4,"low", boundary5, boundary4)

}

And it doesn't work as it seems that the table cannot accept a measure for the values.

As soon as I replace the table's code by hard coded values, it works.

Any help would be much appreciated!
Thanks,
Stan

Re: DAX table with dynamic values based on measures

lbendlin — Fri, 09 Feb 2024 01:09:29 GMT

You should not create calculated columns from measures. You cannot create calculated tables from measures (unless it is a single row single column table). In both cases your measure will use a non-existent filter context.

The maximum you can do is create a table variable as an intermediate step in a measure definition.

Re: DAX table with dynamic values based on measures

Standef — Fri, 09 Feb 2024 08:30:13 GMT

Thanks lbendlin ! How would I create such table variable ?

Re: DAX table with dynamic values based on measures

lbendlin — Fri, 09 Feb 2024 13:04:03 GMT

Measure a = Var b= summarize (c,[d]) Return countrows(b)

Re: DAX table with dynamic values based on measures

Standef — Mon, 12 Feb 2024 12:04:43 GMT

Summarize is not able to be dynamic, even built with a measure unfortunately.
Thanks anyway!

Re: DAX table with dynamic values based on measures

lbendlin — Mon, 12 Feb 2024 12:21:55 GMT

That's news to me. Please try it out.