Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to Solution.
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], " ")
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], " ")
Thanks. 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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
105 | |
79 | |
68 | |
61 |
User | Count |
---|---|
143 | |
104 | |
103 | |
82 | |
70 |