Forum Discussion

AngelaB's avatar
AngelaB
Icon for Helper I rankHelper I
3 years ago

DAX command to differentiate between null value and zero in calculated column

Hello   **This post has been removed from Desktop thread and reposted here as I think may be more appropriate**   I've read a few posts along these lines and tried a few of the solutions but none...
DallasBaba's avatar
DallasBaba
Icon for Skilled Sharer rankSkilled Sharer
2 years ago

AngelaB The order of expressions in the statement should not affect the outcome.

 

However, we can try a different approach by using a nested IF statements to ensure that the "0%" is recognized first. 

[calculated%] =
VAR defined_var =
    DIVIDE(
        CALCULATE(
            COUNTA([Value A]),
            [Value A] IN { "Yes" }
        ),
        COUNTA([Value A])
    )
RETURN
    IF(
        defined_var = 0,
        "0%",
        IF(
            ISBLANK(defined_var),
            "Null",
            FORMAT(defined_var, "0%")
        )
    )

 

The above measure checks if defined_var is equal to 0%. If it is, It will return "0%." If not, the statement will proceed to check if it's blank and return "Null" if it is. Otherwise, It will format defined_var as a percentage.

 

This should ensure that "0%" is recognized before checking for null values.

 

If this doesn't work, please send the pbix file of your work sample with the existing relationship between the tables.

 

Thanks

AngelaB's avatar
AngelaB
Icon for Helper I rankHelper I
2 years ago

Hi again DallasBaba . Are you still willing to look at the .pbix file for this? I have tried ChatGPT and enlisting the help of someone with more Power BI experience than I have but still no luck.

 

I'm unable to attached the .pbix file and linked .csv but here is a DropBox link - https://www.dropbox.com/scl/fo/zyhko4e7zr0ucxtdv453i/h?rlkey=z2f4bum5ybcb48eje5pdi4vyu&dl=0 

 

In the above DAX that I was posting, [Value A] = [Mind Active] and the calculated measure is [%COG_IMP].

 

Thanks for your help, I'm still convinced there has to be a simple solution!

Angela