Applying a new filter to a column based on resulting data from slicer selection of another column

[jaswisai]

Hello all! I've been trying to solve this for an embarrasingly long time, but can't seem to think of a solution/ find one online.

Here's what I am trying to do with the data (below):
1. I want to have a slicer for product_details, this slicer will have values of product_details that only belong to one product_category (Category X).
2. Now, I want  a Bar chart, that ranks the products belonging to Category Y, based on their presence in  transaction_ids that contain the product selected using the slicer above.

For below data, assuming I have selected Product A in my slicer, I'd get a bar chart with rankings of Category Y products that are present in the transaction_id's that contain Product A.
Data:

transaction_id	product_category	product_details
TXN001	Category X	Product A
TXN001	Category X	Product B
TXN001	Category Y	Product C
TXN001	Category Y	Product C
TXN002	Category X	Product E
TXN002	Category X	Product F
TXN002	Category Y	Product G
TXN002	Category Y	Product H
TXN003	Category X	Product I
TXN003	Category X	Product A
TXN003	Category Y	Product J
TXN004	Category X	Product B
TXN004	Category Y	Product C
TXN004	Category Y	Product G
TXN005	Category X	Product E
TXN005	Category Y	Product D
TXN005	Category Y	Product J

Desired Output:

Explanation of output: TXN001 and TXN003 contain Product A (selected in slicer, belongs to Category X). In these transaction_ids, the products in Category Y, arranged in decreasing order of frequency are Product C at count 2 and Product J at count 1.

Note: At a smaller scale, I created a new table from the above data, that collects unique transaction_ids for a specific product and then applied it as a filter on transaction_ids for main data. However, there are far too many products now to be able to create a table of transaction_ids for each product.

Thanks for your time!

[v-xiaocliu-msft · Accepted solution]

Hi @jaswisai ,

I made a sample for you.

Measure = var test =  VALUES(Table4[transaction_id])

var test2 =  COUNTROWS( FILTER(ALL(Table4), [transaction_id] in test && 'Table4'[product_details]=MAX('Table'[product_details]) ))

var test3 =IF( MAXX( FILTER(ALL('Table4'),'Table4'[product_details]=MAX('Table'[product_details])),'Table4'[product_category])="Category X",0,test2)

RETURN test3

Best Regards,

Wearsky

[jaswisai]

Thanks @v-xiaocliu-msft ! The approach works!
I'm not that well-versed in DAX yet, so please let me know if my understanding is correct.

Table collects all unique product details.
The measure:
-Var Test, collects all unique transaction ids from Table4 (base data)
-Var Test2, counts rows (occurences) of the product selected via slicer? (Table4, where transaction id in Test and product details = MAX(Table[product_details). Here MAX(Table[product_details) would simply be the value selected via slicer?

-Var Test3, very lost on this. Inside MAXX, Table4 is filtered only to have rows that contain MAX(Table[product_details]), which would be rows of Table4 that contain the product selected in slicer. MAXX of this would simply be Category X? Since that is all that remains? And IF statement checks if this is equal to Category X, then it returns 0, else it returns Test2...?

I'm definitely confused about the implementation, so I'd appreciate your help in udnerstanding your solution 🙂