DAX Filter on same Table

[Raivo_S]

Hello,

I am struggling to create to create formula, that would filter table based on values from the same table. I created this table of individual sales as an example:

It shows client who bought product, product which he bought and seller, who sold it.

It will be wierd example, but lets say, that I want to mark those sales of product "BBB", where the same client has also bought product "AAA" from the same seller. It means that in my example only one row should be checked, but my formula checks all rows that contain product "BBB". I belive that my problem is in filters, but I don't know how to fix it. 

I would also appreciate if you could give me formula for a mesure, that would count clients, who match same criteria (has bought Product "BBB" and "AAA" from the same seller). It is for another similar problem, where I don't really want to create another collumn.

Thank you in advance!

[Ashish_Mathur · Accepted solution]

Hi,

This calculated column formula works

=if(Data[Product]="BBB",CALCULATE(COUNTROWS(Data),FILTER(Data,Data[Client_ID]=EARLIER(Data[Client_ID])&&Data[Product]="AAA"&&Data[Seller]=EARLIER(Data[Seller]))),BLANK())

[Raivo_S]

It worked, thank you!

I tried to create those filters using function EARLIER previously but made a mistake in my formula. Just tried to write another one from the beginning and it worked perfectly!

I will leave this topic open until I make my Measure, then I will share my solution and close this topic.