Forum Discussion

weilai0521's avatar
weilai0521
Frequent Visitor
7 years ago
Solved

Exclude missing values with calculate

Hi,   I have a formula like this to calculate percentages which can be changing dynamically with age, gender filters. But there are some 'DATA'[XXX] with missing values "." and I want to exclude th...
  • Stachu's avatar
    Stachu
    7 years ago

    add the 

    DATA[XXX]<>"."

    to your denominator calculate, like this:

    DIVIDE (
    CALCULATE (
        SUM ( 'DATA'[Weight] ),
        FILTER (
            'DATA',
            'DATA'[XXX] = "Yes"
                && 'DATA'[Age] = [Selected_Age]
                && 'DATA'[Gender] = [Selected_Gender]
        )
    ),
    CALCULATE (
        SUM ( 'DATA'[Weight] ),
        ALLEXCEPT ( 'DATA', 'DATA'[Year], 'DATA'[Age], 'DATA'[Gender] ),
        DATA[XXX] <> "."
    )
)
  • weilai0521's avatar
    weilai0521
    7 years ago

    Thank you! This is very helpful!

Anonymous's avatar
Anonymous
Not applicable
2 years ago

hello, 

 

this solution of removing missing in calculate function (dax) doesn't seem to work for me. Perhap because the calculate is based on other measures which already have filter in them. I ended up just using simple If(isblank(variable), blank(), calculate(variable - variable2).