Forum Discussion
Resolver IFind most common text value with criteria
In Excel, I'm able to find the most common text value in a Table column based on criteria using the following expression (table provided as a representation):
Table
| ID (Column A) | Class (Column B) | Value (Column C) |
| 001 | A | Red/A |
| 002 | B | Red/B |
| 003 | C | Blue/C |
| 004 | C | Red/C |
| 005 | A | Red/A |
| 006 | A | Green/A |
| 007 | B | Blue/B |
Generic Expression
= INDEX(range, MODE(IF(logical, MATCH(range, rage, 0)
Specific Expression (in this case, looking for the most common Text value in column C (Value) that matches the Class with "A")
= INDEX('Table'!C:C,MODE(IF('Table'!B:B="A",MATCH('Table'!C:C,'Table'!C:C,0))))
In the example above, the cell with that expression in it would return Red/A since that's most common Value value for Class A in the Table.
Is it possible to do something similar in Power BI (i.e., create a measure) and display the value in a Card visualisation? I cannot figure out how to translate this into a DAX expression that I can use in Power BI.
If you only ever need the top one value, you could try
measure =
VAR _table =
SUMMARIZE (
'Table',
'Table'[Class],
'Table'[Value],
"count", DISTINCTCOUNT ( 'Table'[ID] )
)
VAR maxcount =
MAXX ( _table, [count] )
RETURN
CALCULATE (
SELECTEDVALUE ( 'Table'[Value] ),
FILTER ( _table, [count] = maxcount )
)Which will give you "Red/A" for A, but blank for B and C (as there is not one most common value).
To use it in a card, you would need to filter the card with Table[Class], to see the most common for specifically A, B or C. You could also include the filter in a measure
measure A = CALCULATE ( [measure], Table[Class] = "A" )
If you want to see the most common values which tie with each other (such as for B and C), then you can replace the SELECTEDVALUE ( 'Table'[Value] ) with CONCATENATEX(VALUES('Table'[Value]), 'Table'[Value], " ")
3 Replies
- AntonioM
Solution Sage
If you only ever need the top one value, you could try
measure =
VAR _table =
SUMMARIZE (
'Table',
'Table'[Class],
'Table'[Value],
"count", DISTINCTCOUNT ( 'Table'[ID] )
)
VAR maxcount =
MAXX ( _table, [count] )
RETURN
CALCULATE (
SELECTEDVALUE ( 'Table'[Value] ),
FILTER ( _table, [count] = maxcount )
)Which will give you "Red/A" for A, but blank for B and C (as there is not one most common value).
To use it in a card, you would need to filter the card with Table[Class], to see the most common for specifically A, B or C. You could also include the filter in a measure
measure A = CALCULATE ( [measure], Table[Class] = "A" )
If you want to see the most common values which tie with each other (such as for B and C), then you can replace the SELECTEDVALUE ( 'Table'[Value] ) with CONCATENATEX(VALUES('Table'[Value]), 'Table'[Value], " ")
ddaltonThanks. I'll give this a try. The real data will rarely (if ever) have a scenario where the number of Column C values are the same so your solution should always return a value.
I'll try it, and update/accept the solution if it works out for me.
v-henryk-mstfCommunity Support