Count unique text value occurrences across multiple columns DAX

[Fair-UL]

I am trying to count the number of occurances for "No" across multiple columns ( I have 18 columns that I need to sum the No values for each row). Data as follows. What I need is the column "Results". 

Qu1	Qu2	Qu3	Qu4	Results
Yes	N/A	Yes	Yes	0
Yes	No	N/A	N/A	1
Yes	Yes	No	Yes	1
Yes	N/A	No	No	2
Yes	Yes	Yes	Yes	0

Thanks!

[v-yingjl]

Hi @Fair-UL ,

You can create a calculated column to achieve this:

Results = 
VAR _rows = { [Qu1], [Qu2], [Qu3], [Qu4] }
VAR _count =
    COUNTROWS ( FILTER ( _rows, [Value] = "No" ) )
RETURN
    IF ( _count > 0, _count, 0 )

Best Regards,
Yingjie Li

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[parry2k · Accepted solution]

@Fair-UL I would recommend to unpivot your data, select the columns which are not QU1, QU2..... if you have the only columns from QU1 to QU18 then add an index column.

- transform data
- select index column in the table
- right-click, unpivot other columns it will add two columns, attribute, and value, rename these as per your requirement
- close and apply

To visualize,  add a measure

No Count = 
CALCULATE ( COUNTROWS ( Table ), Table[Value] = "No" ) 

- add a matrix visual:
- add attribute on columns,
- add No Count measure on values section

and you will get the count.

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡