cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
fac01
New Member

Most repeated value on the same row, different columns

Hi all,

I'm trying to get the most repeaed value on the same row, but on different colums, any idia of how to do this?

My table:

NameID_1ID_2ID_3Result
aaXXX1XX3XXX1XX1
bbZZ2ZZ2 ZZ2
ccMM3  MM3

trying to get column "Result" counting the most repeated value on ID columns

 

Thanks!!

1 ACCEPTED SOLUTION
Jihwan_Kim
Super User
Super User

Hi,

Please check the below picture and the attached pbix file.

 

Picture1.png

 

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 )
RETURN
SWITCH (
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

Linkedin: https://www.linkedin.com/in/jihwankim1975/

Twitter: https://twitter.com/Jihwan_JHKIM

View solution in original post

2 REPLIES 2
Jihwan_Kim
Super User
Super User

Hi,

Please check the below picture and the attached pbix file.

 

Picture1.png

 

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 )
RETURN
SWITCH (
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

Linkedin: https://www.linkedin.com/in/jihwankim1975/

Twitter: https://twitter.com/Jihwan_JHKIM

View solution in original post

Hi Kim, thanks for your help, it works perfectly!!!

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.