Forum Discussion
DAX(Using MAXX)
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
Hi,
Yeah it is possible, you just need to add MAX's iteratively. E.g.
7 Replies
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!
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!
MeghaHi,
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])) returnif(_max>7,7,_max+1)