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

[Uzi2019]

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.

[amitchandak]

@Uzi2019 , Try like

=SWITCH(true(),
maxx(filter(product, [product] ="Travel"),[product]) = "Travel",[Travel]
maxx(filter(product, [product] ="TW"),[product]) = "TW",[Tw]
,[Old column])

[Uzi2019]

Hi @amitchandak 

Thank you for your reply. But it is not working showing value 1200 if i select other product like car n all. and Total sum is not showing.

Please guide how to resolve this issue.

[amitchandak]

@Uzi2019 ,Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[Uzi2019]

Hi @amitchandak 

Basically there are 2 tables. Both are connect on key column. Rest of the condition is same as above I have mentioned.

This is product table

Date	product	sales	Key column
1/2/2020	Car	2000	1/2/2020Car
2/3/2020	health	4000	2/3/2020Heatl
1/3/2020	TW	1000	1/3/2020TW
5/2/2020	Home	2000	5/2/2020Home
8/10/2020	Car	1200	8/10/2020Car
6/5/2020	Health	500	6/5/2020Health
1/2/2020	Home	3000	1/2/2020Home
1/7/2020	TW	2000	1/7/2020TW
23/4/2020	Travel	3000	23/4/2020Travel

This is Primary key table

date	product	Key column
1/2/2020	Car	1/2/2020Car
2/3/2020	Health	2/3/2020Heatl
1/3/2020	TW	1/3/2020TW
5/2/2020	Home	5/2/2020Home
8/10/2020	Car	8/10/2020Car
6/5/2020	Health	6/5/2020Health
1/2/2020	Home	1/2/2020Home
21/7/2020	TW	21/7/2020TW
23/4/2020	Travel	23/4/2020Travel
1/2/2020	Health	1/2/2020Health
1/2/2020	TW	1/2/2020TW
1/3/2020	Home	1/3/2020Home

[v-janeyg-msft · Accepted solution]

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

If it doesn’t solve your problem, please feel free to ask me.

Best Regards

Janey Guo

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Uzi2019]

Hi @v-janeyg-msft 
 Thank you for your time. Let me check your formula at my end ,will get back to you.