How to select multiple values from filter and create condition base calculated field.

Hi Experts,

I want to do calculation based on filter selection. If user select "TW" the "new " should display "3700" and if user select "Travel" the "new " should display "1200" or otherwise " Old column".

1) Travel= IF(MAX([Product])="Travel",3700)

2) TW= IF(MAX([Product])="TW",1200)

3) Old column = ( sum(A)+sum(B) ) / ( sum(C)+sum(D) ).

4) new =SWITCH(SELECTEDVALUE([product]),"Travel",[Travel],"TW",[Tw],[Old column])

* All Calculated fields are measure.

Everything is working fine if user select single value from filter. but if I select travel and TW it is showing incorrect result.

Please guide me to solve this issue. because selectedvalue only supports single value.

Thank you in advance.

Need Help

v-janeyg-msft

5 years ago

Hi, Uzi2019

It’s my pleasure to answer for you.

According to your description, Do you want to calculate the sum of the values when a date contains multiple products.

You can try like this:

Measure =
VAR p =
    ALLSELECTED ( 'product' )
RETURN
    SUMX (
        SUMMARIZE (
            'product',
            'product'[Date],
            "test",
                VAR e = [Date]
                VAR a =
                    ADDCOLUMNS (
                        p,
                        "value", SWITCH ( [product], "Travel", [Travel], "TW", [TW], 1000 )
                    )
                VAR b =
                    SUMMARIZE (
                        a,
                        'product'[Date],
                        "sum", SUMX ( FILTER ( a, [Date] = EARLIER ( 'product'[Date] ) ), [value] )
                    )
                RETURN
                    SUMX ( FILTER ( b, [Date] = e ), [sum] )
        ),
        [test]
    )