COUNTAX with multiple filters not working as expected with 0 and 1 flags.

[Anonymous]

I have a COUNTAX measure with two filters, but it's not counting my rows that will give me an employee count. I get this error message:

MdxScript(Model) (10, 17) Calculation error in measure 'AdvisorPerformance'[_M_EmpAdvActiveCountax]: DAX comparison operations do not support comparing values of type Text with values of type Integer. Consider using the VALUE or FORMAT function to convert one of the values.

This is my measure. The active column has a flag on it with 1 or 0. Power BI sees it as text, so I threw quotes around the 1 and thought that would do it, but it doesn't work. What I get is (blank) in my card. Without quotes around the 1, it won't even display that. 

_M_EmpAdvActiveCountax = 
COUNTAX(
        FILTER(
                AdvisorPerformance,
                AdvisorPerformance[Employee.Employee Type]="Advisor" &&
                AdvisorPerformance[Employee.active]="1"
                ),
        AdvisorPerformance[Employee.rfc_id]
)

How can I fix this? Is COUNTAX with filters not the best way?

[aj1973]

Hi @Anonymous 

Try to use TRUE() function instead of "1", see how it works.

[Anonymous]

Like this? It didn't work. What else could I try? 

_M_EmpAdvActiveCountax = 
COUNTAX(
        FILTER(
                AdvisorPerformance,              
                AdvisorPerformance[Employee.active]=TRUE()
                ),
        AdvisorPerformance[Employee.rfc_id]
)

[aj1973 · Accepted solution]

You need to open Power Query and modify the type of the Field Employee.Active to either TEXT or True/False

[Anonymous]

Something strange happened. When I imported my data, my 1s and 0s turned to true and false, but pbi only recognized the true and false as text. So ="True" rather than =TRUE() was the way to do it. Thanks for the help.