DAX: Issues Calculating a Sum with Multiple Filters

Question

I thought I'd reach out for help as I'm having troubles creating a calculated measure in PowerBI. I'm trying to calculate the sum of multiple accounts using filters to grab the correct accounts.

Here is the measure that isn't working:

Measure =
CALCULATE (
    SUM ( 'Queryx'[Amount] ),
    FILTER (
        Queryx,
        LEFT ( 'Queryx'[Accnt], 4 )
            IN { "8980", "8981", "8982", "8983", "8987", "8988", "8989" }
    ),
    FILTER ( 'Queryx', [Accnt] IN { "89660", "89700", "89850" } ),
    FILTER ( 'Queryx', LEFT ( 'Queryx'[Accnt], 3 ) IN { "899" } )
)

If I run the measure with only 1 filter the measure works, but the addition of the other 2 filters throws a wrench in the system. Any idea how I could get this to run? Or is there a smarter way to go about creating this measure?

Thanks for taking the time to read this and I greatly appreciate any help =)

What do you mean by it "isn't working"? What is your expected result and what are you currently getting? — Alexis Olson

Alexis Olson Alexis Olson · Accepted Answer · 2019-02-27T23:10:42

The filters are all applied using AND logic. I suspect you want to use OR logic since it's not possible to satisfy all those conditions at once.

Try something more like this:

Measure =
CALCULATE (
    SUM ( 'Queryx'[Amount] ),
    FILTER (
        Queryx,
        LEFT ( 'Queryx'[Accnt], 4 )
            IN { "8980", "8981", "8982", "8983", "8987", "8988", "8989" }
          || [Accnt] IN { "89660", "89700", "89850" } ),
          || LEFT ( 'Queryx'[Accnt], 3 ) IN { "899" } )
    )
)

The || is the logical OR syntax.

DAX: Issues Calculating a Sum with Multiple Filters

1 Answers