Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi Community,
I need a DAX to find count of a related product based on selection of product selected in the filter.
Example: Consider A as Product , if I select Product A, I want to know the count of Product A and Count of Other Products which are purchased with Product A.
Person 1 buy a Pen with Pencil
Person 2 buy a Pen with Eraser
Person 3 buy a Pencil with Eraser
If I filter Pen then I need count of Pen Purchased and Related Product Count
Such as Product Filter:Pen Count :2 Related Product:Pencil Count:1
Eraser Count:1
Somewhat similar to this
Thanks In Advance
Amz
Solved! Go to Solution.
Hi @Amz_123
You may create measures like below.Attached sample file for your reference.
count of product = IF ( ISFILTERED ( Slicer[Product] ), CALCULATE ( COUNT ( Table1[Product] ), FILTER ( Table1, Table1[Product] IN VALUES ( Slicer[Product] ) ) ) )
Count of Other Products = VAR a = CALCULATETABLE ( VALUES ( Table1[Person] ), FILTER ( ALL ( Table1 ), Table1[Product] IN VALUES ( Slicer[Product] ) ) ) RETURN CALCULATE ( COUNT ( Table1[Product] ), FILTER ( Table1, Table1[Person] IN a && Table1[Product] <> SELECTEDVALUE ( Slicer[Product] ) ) )
Regards,
Hi @Amz_123
You may create measures like below.Attached sample file for your reference.
count of product = IF ( ISFILTERED ( Slicer[Product] ), CALCULATE ( COUNT ( Table1[Product] ), FILTER ( Table1, Table1[Product] IN VALUES ( Slicer[Product] ) ) ) )
Count of Other Products = VAR a = CALCULATETABLE ( VALUES ( Table1[Person] ), FILTER ( ALL ( Table1 ), Table1[Product] IN VALUES ( Slicer[Product] ) ) ) RETURN CALCULATE ( COUNT ( Table1[Product] ), FILTER ( Table1, Table1[Person] IN a && Table1[Product] <> SELECTEDVALUE ( Slicer[Product] ) ) )
Regards,
Hi,
just import data in Power bi.
Use slicer dropdown option for product and add product and count into the table.
you will get the desired result.
let me know if you need help and share some data
Hi @adityavighne ,
This is not what I asked,
I need DAX, If I select Pen I what Pen count with the count of product which I purchased with Pen. Based on above example which I said in above message says Person 1 and Person 2 brought Pen along with 1 Pencil and 1 Eraser.
Here you have shown only the total count of Pen I need the count of Pencil and Eraser Since its purchased by Person 1 and Person 2.
So, while filtering Pen I want to get,
Pen=2
Pencil=1
Eraser=1
while filtering Pencil,
Pencil=2
Pen=1
Eraser=1
while filtering Eraser,
Eraser=2
Pen=1
Pencil=1
Thanks
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
126 | |
106 | |
105 | |
86 | |
72 |