A measure including two variables from two filters on the same page?

[Anonymous]

Hello everyone

I am trying to caculate the degree of difference across two events, based on which countries users select.

How can I create a measure to realize (Value of weeks_out of 11 in Paris, 2018) / (Value of weeks_out of 11 in Bogota, 2017) -1?

I guess I need to know how to include two variables from two page level filters on the same page.

Thank you in advance! 

(P.S. the event can be any two years and may not be consecutive, e.g. 2012 vs. 2016, 2018 vs. 2014)

[v-jayw-msft]

Hi @Anonymous ,

Please check the following steps.

1# Create a calculated table. There's no relationship between Table and Table 2

Table 2 = DISTINCT('Table'[event])

2# Create slicers with event and event 2.

3# Create measures.

Measure = CALCULATE(SUM('Table'[weeks_out]),FILTER(ALL('Table'),'Table'[event]=SELECTEDVALUE('Table'[event])))

Measure 2 = CALCULATE(SUM('Table'[weeks_out]),FILTER(ALL('Table'),'Table'[event]=SELECTEDVALUE('Table 2'[event])))

compared = [Measure]/[Measure 2]-1

 Result would be shown as below.

Best Regards,

Jay

Community Support Team _ Jay Wang

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

[Anonymous]

Hi Jay, thanks!

If I need to allow users to select more than one option (which is event) here, can I still use "SELECTEDVALUE"?

If not, are there any other way to solve this case?

Thanks again.

[v-jayw-msft]

Hi @Anonymous ,

Then you can use VALUES() function instead.

Please check the formula below.

Measure = CALCULATE(SUM('Table'[weeks_out]),FILTER(ALL('Table'),'Table'[event] in VALUES('Table'[event])))

Measure 2 = CALCULATE(SUM('Table'[weeks_out]),FILTER(ALL('Table'),'Table'[event] in VALUES('Table 2'[event 2])))

Best Regards,

Jay

Community Support Team _ Jay Wang

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

[Anonymous]

Sorry folks... things are not going well and I did not get what I need yet... 

I just draw out my data structure and expect result.

Hope those help.

[Greg_Deckler]

Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

I would create 2 independent slicers that would have underlying different tables. Probably disconnected country tables. Then you could use SELECTEDVALUE to grab the selections. Other than that, hard to be specific with the information provided.