This problem is not the traditional dynamic slicer (calculating totals for either revenue, costs or profit). I would like to create a dynamic slicer which allows me to explore a lookup table of customer. I hope I can abstract my specific problem into a generic problem that helps explain the challenge:
- I have a customer table (lookup), sales transactions by date and product (fact)
- Meeting categories: I have a customer meetings by type, linked to customer lookup table
- Product Categories 1: I have a products table listing product categories, and this table is linked to the products in the sales table
- Product Categories 2: I have a second product category table listing products and categories, also linked to sales table
“Slicer 1” lets you choose how you will be filtering customers, and summarizing sales by choosing from “by meeting category”, “by product category 1” or “by product category 2”
“Slicer 2” Dynamically changes the list of multiple selectable items based on slicer 1
I figured out the slicers as follows:
A “hanging” table with a row for each value in the 3 filter categories and an extra column called “type” which can be a 1,2 or 3. Example:
Triple filtering Table=
ADDCOLUMNS(SUMMARIZE(‘Customer Meetings’,[Meeting Category]),“Type”,1),
ADDCOLUMNS(SUMMARIZE(‘Products Categorization 1’, [Category]),“Type”,2),
ADDCOLUMNS(SUMMARIZE(‘Products Categorization 2’, [Category]),“Type”,3)
), [Category]<> BLANK())
This way the first slicer chooses one of 3 values from a custom table and the second slicer filters the union table looking for a particular type.
This is as far as I advanced. Clearly it wont work because you can not create relationships between the type 1 records and the customer meetings table.
I hope my description is clear and you can think of a creative approach?