Slicer does not remove columns in matrix with DAX switch for rows

[DNKL]

Hi all, 

As I have tried to explain with the picture below, I am running into a problem with filtering a matrix table. 

The rows of the matrix contain measures with use of a switch formula, and the columns contain customer names. Each customer name is connected to a country, and I would like to be able to filter the matrix by selecting a country in the slicer. However, when doing so, the matrix still gives the correct values of the measures, but it does not remove the unnecessary columns - which are resellers that are not associated with that country. 

For example, when selecting Austria, I would only like to see the columns for customer E, H, K and P, and not for all customers. 

I am guessing this has something to do with the fact that the measures do not return blanks, and thus there will always be a value and the column will thus not be removed by default. However, I still want to display customers in the matrix that have zero values, as long as they are included in the customer name slicer. 

Is there another way in which I can make this filter apply to the matrix, even with use of DAX or so? 

Please let me know if you need further clarification of the problem. 

Thank you in advance!

[v-yuta-msft]

@DNKL ,

Please check if you have enabled the interaction between the two slicers. In addtion, in you want the measure changes automactically in the matrix, you should use some dax functions which can interact with the selection in slicers like SELECTEDVALUE() or IN VALUES(Table[Value]).

In addtion, if you still can't solve this issue, it's better to provide some sample data and give the expected result so that I could do further analysis.

Community Support Team _ Jimmy Tao

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

[DNKL]

Hi @v-yuta-msft and @amitchandak ,

Thank you both for thinking along! 😊

All 3 slicers interact correctly to filter the matrix, and the slicers also filter each other. 

I think the issue might be resolved with some addition to the DAX, but I am not sure what to add to ensure that if a country name is selected, then the customer name is filtered correctly. Currently, this is the switching measure in DAX: 

Switch measure =

SWITCH([Selected Module],

2,[Module 2 last full month],

4,[Module 4 last full mth],

5,[Module 5 MTD],

blank())

I guess that by adding a part before the 'SWITCH([Selected Module]', it should be possible to determine the columns, but I am getting stuck because I am not sure how to combine the SELECTEDVALUE('D_Countries'[Country.Country Name]) and the VALUES('D_Customers'[Customer.Customer Name]). Or perhaps I am way off and it should be done completely differently 🙂 

How would you approach writing this solution in DAX? 

[DNKL · Accepted solution]

Hi all, 

I solved the issue by writing a simple DAX before the SWITCH which did the trick:

Switch measure = 

if(
contains(
D_Customers,D_Customers[Customer.Customer Name],
SELECTEDVALUE(
D_Customers[Customer.Customer Name])
),
SWITCH([Selected Module],
2,[Module 2 last full month],
4,[Module 4 last full mth],
5,[Module 5 MTD],
blank())
,blank())

[amitchandak]

Check is an issue because the interaction is closed?