DAX Formula not working as it thinks i'm using Text

[Anonymous]

Hi there,

I have an embedded measure doing three things:

1. If a Referred to IHAT? Yes/No column is set to FALSE then produce blank. Else move to 2:

2. Calculate the number of days between two dates using a calendar table which removes holidays and weekends 

3. If the number of dates is greater than or equal to 3 - produce a zero. Else, produce a one. 

Here is the formula, which doesn't work. I've played around with it a bit but it's got a real problem with my true/false expression:

= IF('GCRT Database'[Referred_to_IHAT?]=FALSE(),0="",IF(CALCULATE(SUM(CalendarYear[WorkDay]),
DATESBETWEEN(CalendarYear[Date],
( 'GCRT Database'[IHAT_Referral_Date]),
( 'GCRT Database'[IHAT_Response_Date])-1))>=3,0,1))

Any help would be greatly appreciated.

Jemma 🙂 

[Greg_Deckler]

Is there an error or is your issue that it just returns wrong values? Can you share sample data? Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[Anonymous]

So I get an error saying:

DAX comparison operations do not support comparing values of type Text with values of type True/False. Consider using the VALUE or FORMAT function to convert one of the values. 

Sample data would just be two date columns with dates in 2018. 

So it seems that the way I have structured the formula thinks i've got text? The dates are definately date columns. 

The result would be a zero, one or blank so i'm not sure what it's problem is. 

I figured it could be something as small as a parenthesis in the wrong place, i've played around but nothing I do is working.

[Anonymous]

If I take out the first IF argument and just have this the following, it works. But of course, if there are no dates in the specified columns, the turnaround days is zero so it's producing a 1. 

So I want to produce a blank for if the IHAT Referral column is FALSE. 

= IF(CALCULATE(SUM(CalendarYear[WorkDay]),
DATESBETWEEN(CalendarYear[Date],
( 'GCRT Database'[IHAT_Referral_Date]),
( 'GCRT Database'[IHAT_Response_Date])-1))>=3,0,1)

[Anonymous · Accepted solution]

Ok I figured it out. It thinks the IHAT referral column is text - so I changed the =FALSE() to ="False" and it's happy.