cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
irnm8dn Member
Member

Convert True False and Count - DAX Help

I have a column in a dataset which is either True or False.  The data speaks to a specific action taken within our business.  I attempted to create an IF statement that reads:

 

If(Table]="False", "Incomplete","Complete")

 

I receive a message  that reads "DAX comparison operations do not support comparing values of type True/False with values of type Text. Consider using the VALUE or FORMAT function to convert one of the values."

 

A workaround would be to copy the column and replace the values, but that seems hacky and so I am looking for something more elegant.

 

Ultimately I need to count "Completes".

 

Questions to be answered:

1.  What is the best way to convert the True/False so that I can use an IF statement(or something like it)?

2.  How can I get a total count of only the rows marked complete?

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Convert True False and Count - DAX Help

Hi @irnm8dn,

 

1. Since your column is a state type you need to use the FALSE() / TRUE() formulas and not the text "FALSE"/"TRUE" so in this case you would use something like this:

Completed = IF(Table[Status]=FALSE(), "Incomplete","Complete")

2. You have several ways of doing this:

       a) Add the Completed column previously calculated on a visual and then select in the filter the status you need to count.

       b) Add the following measure be aware that this is a hard coded filter so if you need to change any filter on this you have to             rewrite your measure however no need to have :

Count_completed_ = CALCULATE(COUNT('Table'[Completed]),'Table'[Completed]="Complete")

      c) Add the following measure to your model, but if you only want to get one of the values you need to put in place the visual     filter as it's done in the a), this is a more dinamic measure since you can have several states in your visual if you use the b)      option you will be restricted to one state. (as you can see below this measure allow you to use more than one value at a time.

Count_Complete =
VAR count_status =
    COUNT ( 'Table'[Completed] )
RETURN
    CALCULATE ( count_status, 'Table'[Completed] = VALUES ( 'Table'[Completed] ) )

Below are the examples for each of the options:

 

status.png

 

Regards,

MFelix



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

Proud to be a Datanaut!




3 REPLIES 3
Super User
Super User

Re: Convert True False and Count - DAX Help

Hi @irnm8dn,

 

1. Since your column is a state type you need to use the FALSE() / TRUE() formulas and not the text "FALSE"/"TRUE" so in this case you would use something like this:

Completed = IF(Table[Status]=FALSE(), "Incomplete","Complete")

2. You have several ways of doing this:

       a) Add the Completed column previously calculated on a visual and then select in the filter the status you need to count.

       b) Add the following measure be aware that this is a hard coded filter so if you need to change any filter on this you have to             rewrite your measure however no need to have :

Count_completed_ = CALCULATE(COUNT('Table'[Completed]),'Table'[Completed]="Complete")

      c) Add the following measure to your model, but if you only want to get one of the values you need to put in place the visual     filter as it's done in the a), this is a more dinamic measure since you can have several states in your visual if you use the b)      option you will be restricted to one state. (as you can see below this measure allow you to use more than one value at a time.

Count_Complete =
VAR count_status =
    COUNT ( 'Table'[Completed] )
RETURN
    CALCULATE ( count_status, 'Table'[Completed] = VALUES ( 'Table'[Completed] ) )

Below are the examples for each of the options:

 

status.png

 

Regards,

MFelix



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

Proud to be a Datanaut!




irnm8dn Member
Member

Re: Convert True False and Count - DAX Help

Thanks @MFelix !

 

This is awesome.  These answers are what makes the community so positive.  Really appreciate it.

 

One last question...

 

Is there a way to conditionally format the value of Complete and Incomplete i.e. Green / Red Text?

 

Thanks.

Highlighted
Super User
Super User

Re: Convert True False and Count - DAX Help

Hi @irnm8dn

 

The conditional formating is only possible in numbers, not with text you can use this other reply I used for another similar question.

 

Regards,

MFelix



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

Proud to be a Datanaut!