Forum Discussion

mijoe's avatar
mijoe
Microsoft Employee
4 years ago

How to filter matrix based on column data

I have a set of data like this:     BaseColumn ColB ColC value 1 y n n value 2 y y y   I want to filter to only value1 because BaseColumn=y while the other columns=n.  The co...
Need Help
v-jingzhang's avatar
v-jingzhang
Community Support
4 years ago

Hi mijoe 

 

Vijay_A_Verma 's solution will transform the underlying data table and filter out rows that don't meet the condition. Have you tried that?

 

If you don't want to transform the underlying table and only want to hide the rows from the matrix visual at the report layer, you can try my method as below. 

 

Create the following measure and put it to the filter pane on the matrix visual. Set its value to 1. 

Flag = 
VAR _baseColumnValue = CALCULATE(SELECTEDVALUE('Data'[Value]),ALL(Data[ColType]),Data[ColType]="BaseColumn")
VAR _allNoCount = CALCULATE(COUNT(Data[Value]),ALL(Data[ColType]),Data[ColType]<>"BaseColumn",Data[Value]="n")
VAR _allCount = CALCULATE(COUNT(Data[Value]),ALL(Data[ColType]),Data[ColType]<>"BaseColumn")
RETURN
IF(_baseColumnValue = "y" && _allNoCount = _allCount,1,0)

 

I have attached the pbix at bottom. Let me know if you have any questions. 

 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.