topic Convert DAX expression into a table in DAX Commands and Tips

Convert DAX expression into a table

Anonymous — Thu, 22 Jul 2021 12:25:35 GMT

Hi all

I need to convert this DAX measure

Measure =

CALCULATE(

MAX('Table 1'[Status]),

FILTER('Table 1','Table 1'[Level]=MAX('Table 1'[Level]))

)

into a new table. I tried with CALCULATETABLE and MAXX but it does not work.

I want to convert because the Sankey diagram I need to use does not accept measures in the axis

The data I have is as follows:

Table 1:

Name	Level	Status
Extract 2D plans	APS	High
Extract 2D plans	EXE	Medium
Extract 2D plans	LIV	Low
Other Function	APS	Medium
Other Function	EXE	Low
Other Function	LIV	High

And what I want is as follows:

Name	APS	EXE	LIV
Extract 2D plans	High	Medium	Low
Other Function	Medium	Low	High

Thanks in advance.

Re: Convert DAX expression into a table

Jihwan_Kim — Thu, 22 Jul 2021 13:03:17 GMT

Link to the pbix file

I suggest pivoting the table in Power Query.

However, the link the pbix file (above) shows two solutions. (Power Query way and DAX).

Re: Convert DAX expression into a table

Anonymous — Thu, 22 Jul 2021 13:27:32 GMT

Jihwan_Kim thanks for your answer.

Actually I need something generic considering the distinct Levels. For example, if I have only APS and EXE, then I don't need the LIV column. However, if I have 4 distincts Levels, then I'll need 4 columns to be added. Would it be possible to generalize in DAX? In Power query I can't use because I have many calculated columns in DAX that are not available in Power query.