Chart legends controlled by 2 different slicers

[itsmeanuj]

Hi Guys,

This is a unique problem (for me at least) that I want to solve.  There is a line chart visual whose legends need to be controlled by 2 different slicers. here is the snippet of the requirement. So the user wants to select a country (or countries) from the top slicer and cluster from the slicer on the right. e.g. User wants to select Belgium from the top dropdown which will show the line for Belgium. Now the user wants to select the value "Mid-Sized Market" from the right slicer and wants to see value for the Mid-sized market as well. The idea is to see how the individual country is performing vs the whole cluster. 

My data looks like this:

Month_num	Sales	Country	Cluster
1	100	Belgium	Mid Sized
1	170	France	Large Sized
1	300	United Kingdom	Large Sized
1	400	Japan	Large Sized
1	500	Germany	Large Sized
1	50	Spain	Mid Sized
1	150	Italy	Mid Sized
1	180	Sweden	Mid Sized
2	120	Belgium	Mid Sized
2	150	France	Large Sized
2	320	United Kingdom	Large Sized
2	440	Japan	Large Sized
2	4890	Germany	Large Sized
2	70	Spain	Mid Sized
2	110	Italy	Mid Sized
2	190	Sweden	Mid Sized
3	110	Belgium	Mid Sized
3	190	France	Large Sized
3	330	United Kingdom	Large Sized
3	390	Japan	Large Sized
3	550	Germany	Large Sized
3	100	Spain	Mid Sized
3	160	Italy	Mid Sized
3	170	Sweden	Mid Sized

if i can get any help on this that would be really apprecialble.

Thanks,
Anuj

[v-junyant-msft · Accepted solution]

Hi @itsmeanuj ,

If I understand you correctly, you want to have a line for Belgium and another line for all countries for "Mid-Sized Market" by selecting two slicers. However after my test, according to your screenshot, you are applying country as a legend, but this has the limitation that you can't add another line in the line chart dedicated to "Mid-Sized Market" values.
So I find a workaround for you:

I alse create a new table to create the slicer for Cluster:

And I use this DAX to create a new measure:

 Sales_Cluster = 
CALCULATE(
    SUM(Sheet2[Sales]),
    FILTER(
        ALL(Sheet2),
        'Sheet2'[Cluster] = SELECTEDVALUE('Table'[Cluster]) && 'Sheet2'[Month_num] = SELECTEDVALUE(Sheet2[Month_num])
    )
)

I then created the visual object as shown in the screenshot:

The results are as follows:

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