Forum Discussion

Fair-UL's avatar
Fair-UL
Icon for Helper II rankHelper II
6 years ago
Solved

Count unique text value occurrences across multiple columns DAX

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". 

Qu1Qu2Qu3Qu4Results
YesN/AYesYes0
YesNoN/AN/A1
YesYesNoYes1
YesN/ANoNo2
YesYesYesYes0

Thanks!

Need Help
  • parry2k's avatar
    parry2k
    6 years ago

    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.⚡

2 Replies

  • parry2k's avatar
    parry2k
    Icon for Super User rankSuper User
    6 years ago

    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.⚡

  • v-yingjl's avatar
    v-yingjl
    Icon for Community Support rankCommunity Support
    6 years ago

    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.