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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
JohnTheBig
New Member

Calculate total number of True or False rows for each column

Hi,

 

I've been trying to solve an issue and I think I found a solution, however, I'm not sure it's the most efficient one.

 

I have a table where I have columns with True or False values.

TableA

IDColumn1Column2Column2

1

TRUEFALSETRUE
2FALSEFALSETRUE
3TRUEFALSETRUE
4FALSETRUEFALSE

 

What I would like to do, is to have a vizualisations where I can check the total number of TRUE and FALSE for each column.

 

I though that it's possible to do it automatically in a vizualisation, however, it seems that it's not and I'm trying to solve this using DAX.

 

What I did was:

 

I created a new table.

I manually typed in True and False in the first column and then used DAX formula to populate the rest of the columns.

 

This is the result I got:

 

TableB

 True_FalseColumn1Column2Column3
TRUE213
FALSE231

 

The formula I used to calculate the total number of TRUE or FALSE in table B:

 

 

Column1 = 
CALCULATE( 
    COUNTROWS( 
        TableA),
    FILTER(
        TableA,
        TableA[Column1] = TableB[True_False] )
)

 

 

I have two questions:

 

1) Is this the best way to do it?

2) Now I need to get for each column the percentage of TRUE values. What would be the best way to do it?

 

 

Thanks a lot!

1 REPLY 1
speedramps
Super User
Super User

In edit query

Change the column1,2,3 to a text data type

Click on the ID column > Transform > unpivot column > unpivot other column

 

Then add Dax measures

Total count = COUNTROWS('Table')

 

Total true = CALCULATE( [Total count], 'Table'[Value] = "TRUE" )

 

% = DIVIDE('Table'[Total true],'Table'[Total count])

 

Click here for a demo 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors