Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to Solution.
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:
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @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:
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsThanks @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.
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
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsI would love to see a less processor-intensive way to do this calc.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
99 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
110 | |
91 | |
84 | |
66 |