IN operator + SWITCH

Question

There must be something very obvious that I'm missing here. I am using SWITCH function to create circa 20 bars on a bar chart that cumulate values from a different table.

SWITCH(
    VALUE(SELECTEDVALUE(Table1[RowNo])),
    1, CALCULATE([m1] + [m2], Table2[Key] IN {"0-5", "05-10"}),
    2, CALCULATE([m1] + [m2], Table2[Key] IN {"10-15", "15-20"}),
    3, CALCULATE([m1] + [m2], Table2[Key] IN {"20-25", "25-30"}),
    ...
)

I thought that I will use IN operator (for the simplicity), instead of using a long version (example for the first bar):

CALCULATE([m1] + [m2], Table2[Key] = "0-5") + CALCULATE([m1] + [m2], Table2[Key] = "05-10")

However, IN operator doesn't seem to work as I thought it would:

CALCULATE([m1] + [m2], Table2[Key] = "0-5") -> returns 10,000

CALCULATE([m1] + [m2], Table2[Key] = "05-10") -> returns 20,000

Long version: CALCULATE([m1] + [m2], Table2[Key] = "0-5") + CALCULATE([m1] + [m2], Table2[Key] = "05-10") -> returns 30,000 (this is what I want)

Short version (with IN): CALCULATE([m1] + [m2], Table2[Key] IN {"0-5", "05-10"}) -> returns 20,000 (incorrect; IN seems to always return the value for the second reference)

Could you please help me adjusting my formula so that it returns the correct result (30,000)? I'm happy to provide some further information if needed.

Edit: here's an exemplary dataset - left table (Table1) and right table (Table2):

In PowerBI, these two tables do not have any relationship in a Data Model. Table2 has two measures: m1 = MAX(Table2[F]) m2 = MAX(Table2[R])

Table1 also has two measures (for comparison purposes):

mSwitch = 
    SWITCH(
        VALUE(SELECTEDVALUE(Table1[RowNo])),
        1, CALCULATE([m1] + [m2], Table2[Key] IN {"0-5", "05-10"}),
        2, CALCULATE([m1] + [m2], Table2[Key] IN {"10-15", "15-20"}),
        3, CALCULATE([m1] + [m2], Table2[Key] IN {"20-25", "25-30"})
    )

mSwitch2 = 
    SWITCH(
        VALUE(SELECTEDVALUE(Table1[RowNo])),
        1, CALCULATE([m1] + [m2], Table2[Key] = "0-5") + CALCULATE([m1] + [m2], Table2[Key] = "05-10"),
        2, CALCULATE([m1] + [m2], Table2[Key] = "10-15") + CALCULATE([m1] + [m2], Table2[Key] = "15-20"),
        3, CALCULATE([m1] + [m2], Table2[Key] = "20-25") + CALCULATE([m1] + [m2], Table2[Key] = "25-30")
    )

As you can see on the screenshot below, there is a difference between mSwitch and mSwitch2 (mSwitch2 returning the correct results):

That's odd. It does seem like it should work. Can you provide an example that can replicate this behavior? — Alexis Olson
– Alexis Olson, Commented May 20, 2020 at 13:10
Thank you very much for your time @Alexis, I just updated my post with a small example. — Justyna MK
– Justyna MK, Commented May 20, 2020 at 13:32
What does Table2 look like and how are m1 and m2 defined? — Alexis Olson
– Alexis Olson, Commented May 20, 2020 at 13:36
Oh is it not visible on my post? Maybe it didn't paste correctly? I pasted a screenshot of both Table1 and Table2 datasets and also the calculations for m1 and m2. Happy to paste it separately if that helps. — Justyna MK
– Justyna MK, Commented May 20, 2020 at 13:37

Alexis Olson · Accepted Answer · 2020-05-20 13:47:16Z

1

Your issue isn't to do with SWITCH or IN but rather how MAX works.

The measures m1 and m2 find the maximum values in columns F and R, respectively, under the given filter context. If you limit the filter context to Key = "0-5" then the maximum is 5,000 for each but if you set Key in {"0-5","5-10"}, then the maximum is 10,000 for each.

If you intend to get a sum, then use SUM instead of MAX for your measures.

answered May 20, 2020 at 13:47

Alexis Olson

40.4k8 gold badges51 silver badges74 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Justyna MK Over a year ago

Oh wow, I wouldn't think that MAX in an underlying measure would cause the issue. Initially, I thought that each would be evaluated separately (give me max on measure1, give me max of measure2, and then return the sum of the two). It looks like I got caught up in the filter context again (which I'm hoping to master in 5-10 years...). Anyways, as always, many many thanks for superb help!

Collectives™ on Stack Overflow

IN operator + SWITCH

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related