Create table from multiple true/false columns using DAX

[boyddt_mn]

I have a table with 12 columns of true/false data. I need to be able to present the number of true values per column. Though I was able to get this done my process is not repeatable nor is it elegant. The process I took was to duplicate the table, group by one of the columns, unpivot the table, filter on True values. Repeat for all 12 rows creating 12 new tables. I then used a append query to combine the 12 tables into a single table. Since doing it this way, I cannot get rid of the 12 new tables because they are referenced by the combined table. I was hoping someone had some ideas on how to do this with DAX.

Original Table

Q1	Q2	Q3	Q4
True	True	False	Null
False	True	True	True
Null	False	True	True

Duplicated, Group by Table

Q1	Count
null	1
False	1
True	1

Unpivoted

Count	Attribute	Value
1	Q1	False
1	Q1	True

Filtered

Count	Attribute	Value
1	Q1	True

[boyddt_mn]

What I ended up doing was to copy the table, delete all of the rows that I didn't need, unpivoted the table and that gave me what I needed. I kept an ID field so that I could relate it to the original table.

Thank you for helping and offering suggestions.

[boyddt_mn]

I will give these ideas a try, thank you.

[v-yingjl]

Hi @boyddt_mn ,

If you want to use DAX to count it, you can unpivot all the columns in power query first, close and apply it.

Create a measure like this to count:

Count =
CALCULATE (
    COUNTROWS ( 'Table' ),
    FILTER (
        ALL ( 'Table' ),
        'Table'[Attribute]
            IN DISTINCT ( 'Table'[Attribute] )
                && 'Table'[Value] = TRUE ()
    )
)

Attached a sample file in the below, hopes to help you.

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ryan_mayu · Accepted solution]

@boyddt_mn 

you can try to unpivot all colums.

Then group by attribute and value columns