cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Gui_Gon Frequent Visitor
Frequent Visitor

Slicer: Multiple selection

Hi all,

 

I'm working on a report and am finding some trouble to show some import/export data with multiple filter selection. 

 

For instance: We have country 1, 2 and 3:

Country 1 exports to country 2 and country 2 exports to country 3.

 

I'd like to display different sums imports and exports in a graph so, for example, if I select:

 

Country 1 -> Display demand from country 1 and export volumes for country 2

Country 2 -> Display demand from country 2 and import volumes from country 1 and export volumes to country 3

Country 3 -> Display demand from country 3 and import volumes from country 2

Countries 1 & 2 -> Display demand from countries 1 and 2 and export volumes to country 3

Countries 1 & 3 -> Display demand from countries 1 and 3 and export volumes to country 2 and imports from country 3

Countries 2 & 3 -> Display demand from countries 2 and 3 and import volumes from country 1

Countries 1 & 2 & 3 -> Display only the demand from countries 1, 2 and 3

 

Summing up, I'd like to know how can I sum different numbers based on different selections. So, how can I program Power BI to sum export volumes from country 1 only when only country 1 is selected or only countries 1 and 3 are selected together but do not sum this data with other selections.

 

Do you think you can help me?

Many thanks in advance,

Guilherme

2 ACCEPTED SOLUTIONS

Accepted Solutions
Gui_Gon Frequent Visitor
Frequent Visitor

Re: Slicer: Multiple selection

Hi Raj,

 

Thank you for your answer, but I didn't unsderstood how can I do it. In the scenario I've mentioned, I'd have only 3 measure names (country 1, country 2 and country 3), however the fields that I intent to sum is different based on the selection that I can make.

 

PBI sample.png

 

Does that make sence?

 

Thanks,

Guilherme

Super User
Super User

Re: Slicer: Multiple selection

1. Create DAX table)Ex: Table_Dax) with 1 column

 

Prompt_Values

=========

Country1

Country2

Country3

Country1&2

Country2&3

Country1&3

Country1&2&3

 

2. Create a slicer based on Table_DAX. Prompt_values

3. Create Measure like this:

 

Measure1=SWITCH( SELECTEDVALUE(Table_DAX[Prompt_values]),

"Country1", <your calculation>,

"Country2",<your calculation>,

"Country3",<your calculation>,

"Country1&2",<your calculation>,

"Country1&3",<your calculation>,

"Country2&3",<your calculation>,

"Country1&2&",<your calculation>

)

 

Hope this helps.

 

Thanks

Raj

 

4 REPLIES 4
Super User
Super User

Re: Slicer: Multiple selection

You will have to create a DAX table by entering the values. The DAX table will have 2 column, one is with the measure name that you want to display and another column(Say ColA) you can give some meaningful value.

 

Your slicer should be based on the ColA column values and based on the selection, you can use the measure dynamically. you need to use SELECTEDVALUE function for this.

 

Thanks

Raj

Gui_Gon Frequent Visitor
Frequent Visitor

Re: Slicer: Multiple selection

Hi Raj,

 

Thank you for your answer, but I didn't unsderstood how can I do it. In the scenario I've mentioned, I'd have only 3 measure names (country 1, country 2 and country 3), however the fields that I intent to sum is different based on the selection that I can make.

 

PBI sample.png

 

Does that make sence?

 

Thanks,

Guilherme

Super User
Super User

Re: Slicer: Multiple selection

1. Create DAX table)Ex: Table_Dax) with 1 column

 

Prompt_Values

=========

Country1

Country2

Country3

Country1&2

Country2&3

Country1&3

Country1&2&3

 

2. Create a slicer based on Table_DAX. Prompt_values

3. Create Measure like this:

 

Measure1=SWITCH( SELECTEDVALUE(Table_DAX[Prompt_values]),

"Country1", <your calculation>,

"Country2",<your calculation>,

"Country3",<your calculation>,

"Country1&2",<your calculation>,

"Country1&3",<your calculation>,

"Country2&3",<your calculation>,

"Country1&2&",<your calculation>

)

 

Hope this helps.

 

Thanks

Raj

 

Nagraju Frequent Visitor
Frequent Visitor

Re: Slicer: Multiple selection

Hi Raj,

 

I followed the same senario for that multi selection in the slicer. but i am not able to select the multiple selections like. column1 and column2 and column3 at single selection.....

 

Can yopu please help me on this.....