cancel
Showing results for
Did you mean:
Helper V

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

1 ACCEPTED SOLUTION
Community Support

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

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.

6 REPLIES 6
Super User IV

@Uzi2019 , Try like

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

Proud to be a Super User!

Helper V

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.

Super User IV

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

Proud to be a Super User!

Helper V

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

Community Support

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

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.

Helper V

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

Announcements