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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Filter customers who are interested in a specific product category

I have the following tables:

Customers_Lookup which contains customerID, name, address ...etc.

Orders_Lookup which contains OrderID, CustomerID, TotalReceipt ...etc.

Product_Lookup which contains ProductID, ProductName, Category ...etc. 

Orders_Data which contains OrderID, CustomerID, ProductID, Quantity, Price ...etc.

 

I need to get a list of customers who never bought a specific product. However, they bought other products that are under that specific product category. For example which customers are interested in Accessories but never bought a Nike Bracelet.

 

Here's what I've done:

Specific Product Category Quantity = CALCULATE([Total Quantity], Products_Lookup[ProductGroup] = "Accessories")

Specific Product Quantity = CALCULATE([Total Quantity] + 0 , Products_Lookup[ProductNumber] = 113000423)

 

Then I've created a visual with Customer, Specific Product Category Quantity, and Specific Product Quantity.

Then I've created a visual filter where (Specific Product Category Quantity > 0) and (Specific Product Quantity = 0).

 

I think this solution isn't flexible as the variables are hardcoded.

Do you have any other recommendations?

1 ACCEPTED SOLUTION
kriscoupe
Responsive Resident
Responsive Resident

 Hi @Anonymous,

 

You are nearly there. You can adapt your formulas to get this working and remove the hardcoding. Try

 

Specific Product Category Quantity = 
CALCULATE(
    [Total Quantity],
    ALL( Products_Lookup )
    VALUES( Products_Lookup[ProductGroup] )
)

Specific Product Quantity = [Total Quantity]

 

Then pop a slicer on the page for Products_Lookup[ProductName]. When you select a product in that slicer the Specific Product Quantity measure will equal sales for that product. The Category Quantity measure will remove any filters from the Product_Lookup table (the ALL) and the VALUES will put filters back on at the ProductGroup.

 

You also don't have to have the measures in the matrix either. You can still use the visual level filters on it.

 

Hope it helps

 

Kris

View solution in original post

2 REPLIES 2
kriscoupe
Responsive Resident
Responsive Resident

 Hi @Anonymous,

 

You are nearly there. You can adapt your formulas to get this working and remove the hardcoding. Try

 

Specific Product Category Quantity = 
CALCULATE(
    [Total Quantity],
    ALL( Products_Lookup )
    VALUES( Products_Lookup[ProductGroup] )
)

Specific Product Quantity = [Total Quantity]

 

Then pop a slicer on the page for Products_Lookup[ProductName]. When you select a product in that slicer the Specific Product Quantity measure will equal sales for that product. The Category Quantity measure will remove any filters from the Product_Lookup table (the ALL) and the VALUES will put filters back on at the ProductGroup.

 

You also don't have to have the measures in the matrix either. You can still use the visual level filters on it.

 

Hope it helps

 

Kris

Anonymous
Not applicable

Thanks a lot, @kriscoupe 

That totally solved the problem.

 

Really appreciate it.

 

Best wishes,

Muhammad Ezzat

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.