DAX(Using MAXX)

[Anonymous]

Hi All,

This is the data I have created in PowerBI. Screenshot is given below:

I have the data in the same format in excel. The screenshot is given below:

Based on the above columns, I created an other column in excel named Weekly cycle. And the formula I implemented was

=IF(MAX(AE3:AR3)>7,7,(MAX(AE3:AR3))+1).

How can I implement the same in data field in PowerBI?

Any help is appreciated!

Thank you!

Megha

[ValtteriN]

Hi,

It is possible to do this kind of comparion by using nested MAX() functions, but I wouldn't recommend it. Instead you can unpivot the data in PQ. 

Multiselect columns with shift click and press the arrow:

Then unpivot columns. Also you can add an index column to keep track of the groups created this way.

In my Example I have 4 columns and index column.
Start data:

After unpivot I have this kind of data:

Dax:

Max with if = IF(MAX('MAX'[Value])>7,7,MAX('MAX'[Value])+1)

End result:

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

[Anonymous]

Hi @ValtteriN ,

Thank you for your reply!

Its not pivoted to unpivot.

And all the columns are calculated columns which is created by me.

I need to implement that formula on the calculated columns in PowerBI.

Can I please know how to do that?

Thank you!
Megha

[ValtteriN]

Hi,

If you want to do this with a calculated column you need to use nested MAX functions e.g.

Column =

var _max = max(MAX('MAX'[Column1],'MAX'[Column2]),max('MAX'[Column3],'MAX'[Column4])) return

if(

_max>7,7,_max+1)

[Anonymous]

Hi @ValtteriN,

Could you please tell if it is possible to use the same for multiple columns, which is more than 4?

Thank you!

Megha

[ValtteriN · Accepted solution]

Hi,

Yeah it is possible, you just need to add MAX's iteratively. E.g.

[Anonymous]

Thank you! @ValtteriN 

[Anonymous]

Is it possible to use the same for multiple columns, which is more than 4?

Thank you!

Megha