Search code examples
excelpowerbidaxmeasure

Count the number of times a specific value appears in a column when there are more than one value in some of the rows

Please is there a measure or DAX in power bi that can be used to count the number of times a specific value appears in a column when there are more than one value in some of the rows?

For example;

Table X

I don' want to unpivot the table because it will mess with the other data in the table.

Result

Table X

Solution

  • Assuming you've already generated a table (named Values) comprising a single-column (named Value) of distinct values ("AA", "AB", "AC", for example), create this measure:

    =
VAR ThisValue =
    MIN ( 'Values'[Value] )
RETURN
    COUNTROWS ( FILTER ( Table1, SEARCH ( ThisValue, Table1[Value],, 0 ) > 0 ) )

    and add it to your visual alongside the Value field from the Values table.