Forum Discussion
Create table from multiple true/false columns using DAX
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 |
Anonymous
you can try to unpivot all colums.
Then group by attribute and value columns
4 Replies
Anonymous
you can try to unpivot all colums.
Then group by attribute and value columns
Hi Anonymous ,
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.
