Forum Discussion
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
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.
Does that make sence?
Thanks,
Guilherme
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
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
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.
Does that make sence?
Thanks,
Guilherme
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