Find most common text value with criteria

[ddalton]

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. 

[v-henryk-mstf]

Hi @ddalton ,

Whether the advice given by @AntonioM  has solved your confusion, if the problem has been solved you can mark the reply for the standard answer to help the other members find it more quickly. If not, please point it out.

Looking forward to your feedback.

Best Regards,
Henry

[AntonioM · Accepted 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], " ") 

[ddalton]

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.