Help to modify DAX code in PBI

[Anonymous]

IF:

HC_Days from SG Date = IF(Master[SG Date]<>BLANK(),Master[SG Date]-TODAY(),BLANK())

HC_90 Days = SWITCH(COUNT(Master[HC_Days from SG Date])<=90,FALSE,"No",TRUE,"Yes")

HC_90 Days is supposed to show the number of HC where the “Days from SG Date” is <= 90 days.

Each row in the table in screenshot below represents a HC.

As you can see, even for the rows where the value of “Days from SG Date” is NOT <= 90 days, e.g. the row where “Days from SG Date” = 145, the HC_90 Days = “Yes” when it should be showing “No”.

Questions:

1. What is wrong in my DAX code that is not giving value “No” when it should, and giving value “Yes” when it shouldn’t;
2. Also, how can I modify the HC_90 Days measure so that instead of the value “No” when it’s FALSE, to show a BLANK instead. I tried that, but I got an error message.

Thanks !

[Anonymous]

Hello! I just realized that the final DAX you gave me actually removed all the rows where the HC_Redeploy is > 90 days. Do you know why. So the results that show now are only rows where HC_Redeploy is <=90 days. We are so close though!

[Wimverh]

Switch the positions of yes and blank in the second IF statement.

or change the formula:

HC_Redeploy =
IF (
    ISBLANK ( SUM ( Master[HC_Days from CSG Date] ) ),
    BLANK (),
    IF ( SUM ( Master[HC_Days from CSG Date] ) > 90, "Yes", BLANK () )
)

[Wimverh]

1. What is wrong in my DAX code that is not giving value “No” when it should, and giving value “Yes” when it shouldn’t;

HC_90 Days = SWITCH(COUNT(Master[HC_Days from SG Date])<=90,FALSE,"No",TRUE,"Yes")

You are doing a count on row level, where it's smaller then 90. So your result of your count will always be 0, 1 or blank()
This is always smaller then 90. 

HC_90 Days =
IF (
    ISBLANK ( Master[HC_Days from SG Date] ),
    BLANK (),
    IF ( Master[HC_Days from SG Date] <= 90, BLANK (), "Yes" )
)

2.Also, how can I modify the HC_90 Days measure so that instead of the value “No” when it’s FALSE, to show a BLANK instead. I tried that, but I got an error message.Use the function BLANK() instead of False

[Anonymous]

Hi, first of all, sorry for any confusion. HC_90 Days was renamed to HC_Redeploy.

That said, I tried the 1st dax - it is giving me error because HC_Days from SG Date is a calculated column so that dax won't work.

[Wimverh]

The first dax was a copy of yours, the second is the changed one
Is HC_Redeploy a measure or a calculated column?

[Anonymous]

HC_Redeploy is a measure

[Wimverh]

If you group more then one row of your source table, 
do you want to test if any of the values is higher then 90, or check if the sum is higher then 90.

I presume that Master[HC_Days from SG Date] is a calculated column

First case: if one of the records is higher then 90, then yes

HC Redeploy =
    IF ( count(Master[HC_Days from SG Date]) > 90, "Yes",BLANK() )

Second Case: if the summary of your selection is higher then 90, then yes

HC Redeploy =
    IF ( sum(Master[HC_Days from SG Date]) > 90, "Yes",BLANK() )

[Anonymous]

Thanks! It worked, except I changed it to: 

HC_Redeploy = IF (SUM(Master[HC_Days from CSG Date])<=90, "Yes",BLANK() )

Can you now modify it so that it only shows "Yes" if value is <= 90, but if there is no value, then also BLANK()).

So, <= 90 then show "Yes"

But if blank (no value) or > 90 then BLANK()

[Wimverh · Accepted solution]

Just wrap it another IF statement, to check for blanks.

HC_Redeploy =
IF (
    ISBLANK ( SUM ( Master[HC_Days from CSG Date] ) ),
    BLANK (),
    IF ( SUM ( Master[HC_Days from CSG Date] ) <= 90, "Yes", BLANK () )
)

[Anonymous]

Thank you!!! EVERYTHING works now! I accepted it as a solution.