I'm trying to get the most repeaed value on the same row, but on different colums, any idia of how to do this?
trying to get column "Result" counting the most repeated value on ID columns
Go to Solution.
Please check the below picture and the attached pbix file.
Result CC =VAR currentname = Data[Name]VAR idone = Data[ID_1]VAR idtwo = Data[ID_2]VAR idthree = Data[ID_3]VAR newtable =GENERATE (CALCULATETABLE ( VALUES ( Data[Name] ), Data[Name] = currentname ),CALCULATETABLE (UNION ( VALUES ( Data[ID_1] ), VALUES ( Data[ID_2] ), VALUES ( Data[ID_3] ) )))VAR idonecount =COUNTROWS ( FILTER ( newtable, Data[ID_1] <> BLANK () && Data[ID_1] = idone ) )VAR idtwocount =COUNTROWS ( FILTER ( newtable, Data[ID_1] <> BLANK () && Data[ID_1] = idtwo ) )VAR idthreecount =COUNTROWS (FILTER ( newtable, Data[ID_1] <> BLANK () && Data[ID_1] = idthree ))VAR maxcount =MAX ( MAX ( idonecount, idtwocount ), idthreecount )RETURNSWITCH (maxcount,idonecount, Data[ID_1],idtwocount, Data[ID_2],idthreecount, Data[ID_3])
If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.
Best regards, JiHwan Kim
View solution in original post
Hi Kim, thanks for your help, it works perfectly!!!
Learn how to create your own user groups today!
Click here to read more about the November 2021 Updates!
Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.