How to Filter a Matrix Instead of the Underlying Data

[Anonymous]

Hello,

I am hoping someone can help with a problem I've been experiencing recently.  Any guidance is greatly appreciated.

I have a visual in PowerBI that involves aggregating/summarizing 2 year's worth of underlying data, where each underlying row contains an Item ID and a few columns of transactional data.  The visual on the front-end shows a summary of those Item IDs and their corresponding transaction Types, based off the time-frame slicer being filtered.

The problem is that when I filter to a certain trans type (summary) being >$x, it filters the underlying data and rules out the lines that I want to continue being summarized.

The solution (which I don't know how to do, hence my post) is to filter the matrix itself, not the underlying data.

Here's what the Matrix Shows:

Location	Item	Item DESCRIPTION	Sales Qty	Sales $	Return Qty	Return $	Exchg Qty	Exchg $
1	12345	Blue Widget	-1	($13.44)	2	$26.88	0	$0.00

Underlying Data:

Location	Date	Item	Item DESCRIPTION	Sales Qty	Sales $	Return Qty	Return $	Exchg Qty	Exchg $
1	9/12/2020	12345	Blue Widget	-1	-$13.44	0	$0.00	0	$0.00
1	2/22/2020	12345	Blue Widget	0	$0.00	2	$26.88	0	$0.00

When I filter the Matrix to Returns >$1, it filters out the total Sales (-1) and shows 0 for that column in the Matrix.  I don't want that, so I would rather filter the Matrix rather than the underlying data.  Is this possible?  Any help is appreciated!

[Anonymous · Accepted solution]

Ok I think I made a work-around.  I created multiple sub-tables based off the master transaciton history table.  One for Sales, one for Returns, and one for Exchanges.  I then made a table showing the Item IDs and Descriptions as a master and linked the tables based of Item ID.  Next, I put the same fields mentioned in my original post into a matrix and now when I filter it seems to work.  

@amitchandak  and @v-lionel-msft  thank you for your help but this is what works for me.

[amitchandak]

@Anonymous , one way is to use the visual level filter and filter measure [Return] >0 (say)

if you filter a measure , then you use a grouping level. All the measures has to follow this

example

new Return = SumX(filter(Summarize(Table, Table[Location],Table[Date], Table[Item], Table[Item DESCRIPTION], "_1", [Return]),[_1]>0),[_1])

new Sales = SumX(filter(Summarize(Table, Table[Location],Table[Date], Table[Item], Table[Item DESCRIPTION], "_1", [Return], "_2",[Sales]),[_1]>0),[_2])

[Anonymous]

@amitchandak thank you for the response but I am not understanding.

I tried putting the field in question into the "Filters on this Visual" under the "Filters" tab but when I filter to >$1 it filtered out the underlying lines that contained sales but no returns

Regarding the second part of your response, I am a little confused...

You said:

new Return = SumX(filter(Summarize(Table, Table[Location],Table[Date], Table[Item], Table[Item DESCRIPTION], "_1", [Return]),[_1]>0),[_1])

• Am I supposed to use this as a Slicer?
• What does "_1" mean?
• Why doesn't "[Return]" have the table name before it?

[amitchandak]

@Anonymous , _1 is new column I am creating in summarize table,

I have assumed you have measure [return] 

here on the grouping of these three Table[Location],Table[Date], Table[Item]

the value will be filtered for measure return after the grouping of data >0. You can make 0 as dynamic using slicer 

[Anonymous]

Is there any other way to do this?  This isn't working.  Why won't the filter on the visual work as expected?

When entering the formula I get the following error and I don't think it would give me what I need anyway. "The syntax for '_' is incorrect."

[v-lionel-msft]

Hi @Anonymous ,

I did not reproduce your problem, would you mind sharing a .pbix file?

Best regards,
Lionel Chen

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