topic Re: DAX(Using MAXX) in DAX Commands and Tips

DAX(Using MAXX)

Anonymous — Fri, 28 Jan 2022 15:59:29 GMT

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

Re: DAX(Using MAXX)

ValtteriN — Mon, 17 Jan 2022 16:27:21 GMT

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!

Re: DAX(Using MAXX)

Anonymous — Mon, 17 Jan 2022 16:38:43 GMT

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

Re: DAX(Using MAXX)

ValtteriN — Mon, 17 Jan 2022 17:00:57 GMT

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)

Re: DAX(Using MAXX)

Anonymous — Tue, 18 Jan 2022 09:46:08 GMT

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

Thank you!

Megha

Re: DAX(Using MAXX)

Anonymous — Mon, 24 Jan 2022 17:23:11 GMT

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

Re: DAX(Using MAXX)

ValtteriN — Mon, 24 Jan 2022 17:48:13 GMT

Hi,

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

Re: DAX(Using MAXX)

Anonymous — Mon, 24 Jan 2022 18:24:26 GMT

Thank you! ValtteriN