Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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 |
Solved! Go to Solution.
you can try to unpivot all colums.
Then group by attribute and value columns
Proud to be a Super User!
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.
I will give these ideas a try, thank you.
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.
you can try to unpivot all colums.
Then group by attribute and value columns
Proud to be a Super User!
User | Count |
---|---|
70 | |
70 | |
34 | |
23 | |
22 |
User | Count |
---|---|
96 | |
94 | |
50 | |
42 | |
40 |