slice selection determine visualization

[Anonymous]

aHi all,

I have a question and am wondering how you implement this in PBI.

For example, I have a slicer below consists of all the states in Australia and the slicer only allows one selection a time.

Hence,  when a user select state "NSW", the visualisation will compute:

1) Spend incurred in NSW

2) Spend incurred in Non-NSW

The following is my two query table models:

Questions - 

The problem that I am facing is ... since the slicer is wither using 'Vendor'[State]  or 'Indigenenous Spend'[State], the moment when I select NSW in the slicer, how is my DAX going to implement the below two items below:

* To exclude NSW to compute the NON-NSW Spend?

* To include NSW to compute the NSW spend?

The problem is I cannot manually assign the value like below as user can select any state anytime. Hence the following two are not possible:

* TargetStateSpend =Calculate(SUM('Indigeneous Spend'[Spend Amount]), filter(''Indigeneous Spend', ''Indigeneous Spend'[State]="NSW"))  

* NonTargetStateSpend = Calculate(SUM('Indigeneous Spend'[Spend Amount]), filter(''Indigeneous Spend', ''Indigeneous Spend'[State] <> "NSW"))  

How to pass the value in the slicer into the above two measurements as variable? 

Thanks.

Tuff

[v-xicai · Accepted solution]

Hi @Anonymous ,

You can create measures like DAX below.

* TargetStateSpend =Calculate(SUM('Indigeneous Spend'[Spend Amount]), ALLSELECTED(''Indigeneous Spend'[State]))  
 
* NonTargetStateSpend = Calculate(SUM('Indigeneous Spend'[Spend Amount]), EXCEPT ( ALL ( 'Indigeneous Spend'[State] ), ALLSELECTED ( 'Indigeneous Spend'[State])))  

Best Regards,

Amy

Community Support Team _ Amy

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