Forum Discussion

DianaT's avatar
DianaT
Helper I
6 years ago
Solved

Transform boolean matrix into count?

Hi all,   I have a data set that looks like this: Is it possible for Power BI to transform the boolean matrix into something like this:   Grad cohort        Count GradPre1970        0 Gr...
Need Help
  • ryan_mayu's avatar
    ryan_mayu
    6 years ago

    DianaT 

    in the PQ, select count column and unpivot other columns.

    then you get a new data layout

    Then create a measure

    Measure = CALCULATE(sum('Table'[count]),FILTER('Table','Table'[Value]="YES"))+0

     

amitchandak's avatar
amitchandak
Super User
6 years ago

DianaT , if you want to keep them as column

 

GradPre1970 new  = if([GradPre1970] ="No",0,1)

for each column and sum up.

In matrix, you can show them on the row.

https://www.burningsuit.co.uk/blog/2019/04/7-secrets-of-the-matrix-visual/

 

You can unpivot the data

https://radacad.com/pivot-and-unpivot-with-power-bi
and use replace function in power query (on right click of column) and convert no to 0 and yes to 1

https://www.youtube.com/watch?v=UZQ9EFvEECY

 

Then you can do simple addition