topic Re: Exclude missing values with calculate in DAX Commands and Tips

Exclude missing values with calculate

weilai0521 — Fri, 21 Jun 2019 14:23:48 GMT

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 those missing values in the denominator. How can I do that?

Thanks!

Lai

Percentage =
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]))
)

Re: Exclude missing values with calculate

Stachu — Sat, 22 Jun 2019 09:55:48 GMT

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] <> "."
    )
)

Re: Exclude missing values with calculate

weilai0521 — Fri, 28 Jun 2019 19:04:43 GMT

Thank you! This is very helpful!

Re: Exclude missing values with calculate

Anonymous — Wed, 11 Oct 2023 19:25:49 GMT

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