Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
boyddt_mn
Helper IV
Helper IV

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

Q1Q2Q3Q4

True

TrueFalseNull
FalseTrueTrueTrue
NullFalseTrueTrue

 

Duplicated, Group by Table

Q1Count
null1
False1
True1

 

Unpivoted

CountAttributeValue
1Q1False
1Q1True

 

Filtered

CountAttributeValue
1Q1True

 

 

1 ACCEPTED SOLUTION
ryan_mayu
Super User
Super User

@boyddt_mn 

you can try to unpivot all colums.

1.PNG

Then group by attribute and value columns

2.PNG3.PNG





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

4 REPLIES 4
boyddt_mn
Helper IV
Helper IV

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
Helper IV
Helper IV

I will give these ideas a try, thank you.

v-yingjl
Community Support
Community Support

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.

unpivot.png

Create a measure like this to count:

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

re.png

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
Super User
Super User

@boyddt_mn 

you can try to unpivot all colums.

1.PNG

Then group by attribute and value columns

2.PNG3.PNG





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.