CODEWITHSUNDEEP
powerbidaxpowerbi-desktop
eatsfood
eatsfood

Reputation: 1088

Counting TRUE() and False() in Power BI Using COUNTAX and FILTER

I have a column (Column_Name) in a table (Table_Name) that contains boolean (0 | 1) values. I am trying to get a count for each value. For example, I have 1500 total rows and I'd like to have 2 Measures that show 700 are True while 800 are False.

I've tried the following, but each just gives me the total number of rows (1500):

False_Measure = COUNTAX(FILTER('Table_Name','Table_Name'[Column_Name]=FALSE()),FALSE())

True_Measure = COUNTAX(FILTER('Table_Name','Table_Name'[Column_Name]=FALSE()),TRUE())

I'm totally confused...

I'm not even sure I'm using the correct DAX functions at this point.

Upvotes: 3

Views: 55641

Answers (2)

Karl
Karl

Reputation: 1724

The problem is in the second parameter of the COUNTAX() function.It should be the column/item you want to count instead of the same listed filter item.

So these functions should give you the results you want:

False_Measure = COUNTAX(FILTER('Table_Name', 'Table_Name'[Column_Name]=FALSE()), [Column_Name])

True_Measure = COUNTAX(FILTER('Table_Name', 'Table_Name'[Column_Name]=TRUE()), [Column_Name])

The documentation for this function is listed here.

Upvotes: 8

Alexis Olson
Alexis Olson

Reputation: 40244

Here's are some possiblities:

True_Measure = SUMX(Table_Name, 1*Table_Name[Column_Name])
True_Measure = SUMX(Table_Name, 1*(Table_Name[Column_Name] = TRUE()))

False_Measure = SUMX(Table_Name, 1*(Table_Name[Column_Name] = FALSE()))
False_Measure = COUNTROWS(Table_Name) - [True_Measure]

In most of these, I'm using 1* to coerce a True/False boolean value to be a number 0 or 1.

You can use a COUNTROWS on a filtered table too.

True_Measure  = COUNTROWS(FILTER(Table_Name, Table_Name[Column_Name] = TRUE()))
False_Measure = COUNTROWS(FILTER(Table_Name, Table_Name[Column_Name] = FALSE()))

If you really want to use COUNTAX, then it would look like this:

True_Measure  = COUNTAX(FILTER(Table_Name, Table_Name[Column_Name] = TRUE()), 1)
False_Measure = COUNTAX(FILTER(Table_Name, Table_Name[Column_Name] = FALSE()), 1)

Upvotes: 5

Related Questions