need 2 Slicers for one line chart, one for column and one for row

[B_Vatani]

Hello, 

I am new to Power BI and faced with this problem which will need your help to fix it.

I have a chart report ( Below Picture) which is related to Map too.

which is based on Project locations and different divisions, 
I already have a slicer for locations, 

but want to have another slicer for divisions (value)

I was able to make a second slicer ( for column) and it works till I just choose one item on the slicer ( just one division) , 
as soon as choosing another division it will not work correctly. 

please let me know how can I make a slicer for columns of a table, based on Multi selection. (much appreciated)

the way I used till now is :

- I made a new table for the division list which can apply slicer on that

 

- then made a new measure for each column

for instance :

Div 03 = IF(MAX('Table'[Div_Silcer])

="Div 03 - Concrete", CALCULATE(MIN('Overall'[Div 03 - Concrete])),BLANK())

since I just start using DAX, I am not sure how can I do this slicer for multiselection.

 I saw in another topic, a solution to find the result of Slicers

https://excelkingdom.blogspot.com/2019/10/how-to-get-all-selected-values-from.html

 I think if I can find a way to search on the result of Slicer selection probably will help ( for instance, is there any " Div 03" on the selected division? ) 

Thank you in advance.

[v-shex-msft]

Hi @B_Vatani,

As MFelix said, your table structure not suitable to do these.

You can 'unpivot columns' on your fields to convert them to attribute and value then it should more simple to coding formulas.

Unpivot columns (Power Query) 

Please check the following blog to know how to create a dynamic attribute filter to interact with the chart graph:

Dynamic Attributes In A Power BI Report 

Regards,
Xiaoxin Sheng

[MFelix]

Hi @B_Vatani ,

To what I can understand you have a column for each division believe that the best option is to use the unpivot option on the query editor and then place the division on the slicer and also on the legend of your chart.

Assuming that your data is like this

Div1 - Div2 - Div3 - Div4

10    -  20   - 30     -  40

If you select allthe columns and unpivot you will get  the following table:

Attribute - Value

Div1        - 10

DIv2        - 20

Div3        - 30

Div4        - 40

Now just use the attribute for the legend and values for the values.

Much easier and no need to have DAX.

[B_Vatani]

Felix, 
thanks for your response, 
but unfortunately, this is not the solution to this problem,
my table already includes unpivot.

my table is like :

location    Div1 - Div2 - Div3 - Div4

City 1         10    -  20   - 30     -  40

City 2         100   -  20   - 20     -  40

City 3         10    -  20   - 30     -  40

City 4         10    -  20   - 30     -  40

I need 2 Slicrera at the same time on Divison and City.
there is no easy way unless as I said before to solve it with DAX,

I will appreciate it if you can help to write DAX as I explained in the main message.

Thanks again,

[MFelix · Accepted solution]

Hi @B_Vatani ,

This table is not unpivot you need to have the information in the following way:

To do it with DAX you need to create a measure similar to this:

DIVISION TOTAL = SWITCH(SELECTEDVALUE(Divsion[Division]);
                        "DIV 1"; SUM('DAX'[Div 1]);
                        "DIV 2"; SUM('DAX'[Div 2]);
                        "DIV 3"; SUM('DAX'[Div 3]);
                        "DIV 4"; SUM('DAX'[Div 4])
                    )

Then use the division table has a legend.

Check PBIX file with both options.

[B_Vatani]

Thank you So much Felix!
you were right about unpivot.
I did all columns together because of that it was not like what you explained,
thank you for explaining that.

I tried both but finally, I used the DAX, since if I unpivot the data since I am using that data on several other reports,  makes it difficult to work on other reports.

thanks again