Convert True False and Count - DAX Help

[Anonymous]

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?

[MFelix · Accepted solution]

Hi @Anonymous,

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:

Regards,

MFelix

[Anonymous]

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.

[MFelix]

Hi @Anonymous, 

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

[charleshale]

I would love to see a less processor-intensive way to do this calc.