Most repeated value on the same row, different columns

[Anonymous]

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:

Name	ID_1	ID_2	ID_3	Result
aa	XXX1	XX3	XXX1	XX1
bb	ZZ2	ZZ2		ZZ2
cc	MM3			MM3

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

Thanks!!

[Jihwan_Kim · Accepted solution]

Hi,

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 )
RETURN
SWITCH (
maxcount,
idonecount, Data[ID_1],
idtwocount, Data[ID_2],
idthreecount, Data[ID_3]
)

[Anonymous]

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