Forum Discussion
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:
| 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!!
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]
)
2 Replies
- Jihwan_Kim
Super User
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]
)