Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Help to modify DAX code in PBI

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 !

 

 

 

Need Help
  • Wimverh's avatar
    Wimverh
    6 years ago

    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 () )
)

10 Replies

  • Wimverh's avatar
    Wimverh
    Resolver IV
    6 years ago

     

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      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's avatar
        Wimverh
        Resolver IV
        6 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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's avatar
      Wimverh
      Resolver IV
      6 years ago

      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 () )
)