Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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())
@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.
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:
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())
Check is an issue because the interaction is closed?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
80 | |
69 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |