Dynamic grouping based on two columns using DAX

[rediano]

Hi all,

I would like to create in DAX a custom column in one of my tables that when I select a filed (A4 Unit in below screenshot) the custom column will get it's value for the respective row and based on grouping from another column will take the value of that field (A4 is Green so Green is included in columns for the respective rows. Anything else becomes null. This needs to be dynamic so when someone is selecting amother value A12 for example then the grouping should be done for yellow.

Is there any solution for this?

Thank you and best,

Redi

[v-yalanwu-msft · Accepted solution]

Hi, @rediano ;

You  need create  a new table as slicer. then create a measure.

1.create a table.

unit slicer = VALUES('Table'[Unit])

2. create a measure.

Measure = 
var _color=CALCULATE(MAX('Table'[Color]),FILTER(ALL('Table'),[Unit]=MAX('unit slicer'[Unit])))
return IF(MAX('Table'[Color])=_color,_color,"null")

The final show:

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.