Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
chinh_ho
Frequent Visitor

Filter intersection between 2 matrix

Hi everyone,
I have been stuck in a situation, and I appreciate any help.
I have a fact transaction and a DimSaler table. I want to make a comparison between 2 people in my organization, so I make another DimSaler called Comparison DimSaler to calculate the difference between 2 people. This is a sample of my data model.

q2.png

This is a sample of the matrix

a1.png

I have 2 slicers to select saler for the matrixs. The first matrix shows sale information of a saler, and the second martix shows other saler. The third table shows the difference sale amount between 2 people. Now, I want to filter on the third table to take only the intersection between matrix 1 and matrix 2, so the third matrix should only contain book, shampoo, and shoes.
I can not post my sample pbix file to this post, so this is a link I refered to created the first 2 matrix. https://www.youtube.com/watch?v=hu_fOtZDJwU
Thank you so much for your help.

@amitchandak

2 REPLIES 2
Greg_Deckler
Super User
Super User

@chinh_ho Maybe:

Measure =
  VAR __Table1 = DISTINCT(SELECTCOLUMNS(FILTER('Transactions',[ID] = 1),"__Type",[Type]))
  VAR __Table2 = DISTINCT(SELECTCOLUMNS(FILTER('Transactions',[ID] = 2),"__Type",[Type]))
  VAR __Common = INTERSECT(__Table1, __Table2)
RETURN
  IF(MAX('Transactions'[Type]) IN __Common, [change amount], BLANK())

Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Hi @Greg_Deckler ,
Thank you so much for your quick response. The Dax calculation did not work for me. I think because the relationship between Transaction and ComparisonSaler is inactive. (I need to make it inactive to filter it on the second table). In addition, I want the Dax dynamic when I select the saler on the slicer. Please help me recalculate it.

a1.png

 

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.