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.
Needing some help figuring out how to set up slicer logic so that a user can bundle multiple attributes together and see what other attributes are also associated with those UPCs that have ALL selected values
Background
I am working on a report that shows the product attributes that are associated with UPC products. So for example, a hot dog UPC may have gluten free, kosher, and other attributes associated with it.
Because there can be 1 to N+1 attributes associated with a given UPC, I’ve had to set up my data model with a bridge table so that when calculating the dollar sales for each UPC I don’t get inflated numbers. The tables I have are:
The existing process is that whenever someone selects single/multiple attributes from the UPC Attributes table, it will resolve those UPC values down to my UPC Details table and give me a unique list of UPCs, and then go get the corresponding dollar sales from the UPC Dollars table.
I have a cross tab set up which shows me the dollar sales for a given attribute, and the user can select values from an existing Attribute slicer to narrow down what is being shown in the view and that all works as expected.
Issue I am Trying to Solve
I’ve been asked to update the report so that a user can also select one or more attributes as a cohort bundle (AND logic), and they can then see what are the other most common attributes also associated with those UPCs. So if I selected kosher and gluten free from the Attribute Cohort slicer, what UPCs have BOTH those values, and what other attributes are also associated to those UPCs and I want to see the results in the existing cross tab.
I’m trying to use a separate Attribute Cohort table to try and implement this, since selecting a slicer attribute should resolve down to the UPCs in the details table and selecting one value seems to work fine. The issue I am trying to figure out is how to select multiple attributes together, and have UPC’s that have ALL those values resolve down to the UPC Detail table.
I’ve included some sample data from each of the tables below, and it all gets joined together via UPC. An example would be that I select Humane Claim and Antibiotics Free from the Attribute Cohort slicer, and would see all attributes in the cross tab associated with UPC values 1394100493 & 2531777530 (and none of the others).
UPC Details
UPC | Product | Department | Category |
2531777530 | JOHNS REFRIGERATED BEEF HOTDOG 6 CT 10 OZ | DEPT-REFRIGERATED | FRANKFURTERS |
61076486381 | PEACH MANGO LIQUID ENERGY DRINK CAN 16 OZ | DEPT-BEVERAGES | ENERGY DRINKS |
7453490164 | FROZEN ICE POP NOVELTY ASSORTED FLAVOR AND SCENT 18 CT 42.3 OZ | DEPT-FROZEN | NOVELTIES - FZ |
1394100493 | REFRIGERATED REGULAR CHICKEN ASSORTED FORM 80 OZ | DEPT-FROZEN | POULTRY - FZ/RFG |
UPC Attributes
UPC | ATTRIBUTE | ATTRIBUTE_TYPE |
2531777530 | All Gluten Free Claims/Certs | Allergens/Intolerances (Free-from) |
2531777530 | Humane Certified | Animal Welfare |
7453490164 | Kosher | Health Conscious/Lifestyle Diets |
1394100493 | Humane Certified | Animal Welfare |
7453490164 | Contains Artificial Flavor | Watch Out Claim: Ingredient |
1394100493 | Non GMO Project Verified | Clean Label/Minimally Processed |
61076486381 | Contains Caffeine (On-Package) | Watch Out Claim: Ingredient |
2531777530 | Whole 30 | Health Conscious/Lifestyle Diets |
1394100493 | Humane Claim | Animal Welfare |
2531777530 | Non GMO Project Verified | Clean Label/Minimally Processed |
2531777530 | All Gluten Free Claims/Certs | Health Conscious/Lifestyle Diets |
1394100493 | Antibiotics Free | Animal Welfare |
61076486381 | Contains Natural Flavor | Clean Label/Minimally Processed |
2531777530 | 100% Grass Fed | Animal Welfare |
61076486381 | Brain Health | Advantaged Ingredient/Wellness Benefit Claim |
2531777530 | Preservative Free | Clean Label/Minimally Processed |
2531777530 | Humane Claim | Animal Welfare |
7453490164 | Allergen Friendly | Allergens/Intolerances (Free-from) |
7453490164 | Contains Natural Flavor | Clean Label/Minimally Processed |
2531777530 | Antibiotics Free | Animal Welfare |
1394100493 | All Natural | Clean Label/Minimally Processed |
1394100493 | Hormone Free | Animal Welfare |
2531777530 | Pasture Raised | Animal Welfare |
2531777530 | Global Animal Partnership | Animal Welfare |
61076486381 | "'""Energy""'" | Advantaged Ingredient/Wellness Benefit Claim |
2531777530 | Casein Free | Allergens/Intolerances (Free-from) |
61076486381 | Artificial Color Free | Clean Label/Minimally Processed |
UPC Dollars
UPC | Dollar Sales |
1394100493 | 210000 |
61076486381 | 10500000 |
2531777530 | 14300000 |
7453490164 | 4400000 |
I’ve gone through so many scenarios that I’m not going to even try listing them out. This example seems a little unique from the ones that I have tried in that instead of the attribute (dimension) to detail (fact) table being a one to many it is instead a many to one, so am hoping that someone might be able to help me out here or point me at solution that would be similar to this?
Thx in advance!
Jeff
Solved! Go to Solution.
This is tricky. You don't want the attribute slicer filtering the attribute list directly but indirectly through the UPC... not simple.
In order to do this, you have to have a new table since the existing slicer column can't be used in the table.
I created DimAttributes table of unique attributes simply defined as
DimAttributes = VALUES ( Attributes[ATTRIBUTE] )
and set up the relationships like this:
Then I defined a new measure and put it on a table with DimAttribute[ATTRIBUTE].
Attribute Sales =
VAR ValidUPCs =
FILTER (
VALUES ( UPC[UPC] ),
CALCULATE ( [HasAllAttributes], ALL ( DimAttributes ) ) = 1
)
RETURN
CALCULATE (
SUM ( Dollars[Dollar Sales] ),
REMOVEFILTERS ( Attributes[ATTRIBUTE] ),
CROSSFILTER ( Attributes[UPC], UPC[UPC], BOTH ),
KEEPFILTERS ( UPC[UPC] IN ValidUPCs )
)
I don't have the energy to explain all of the subtleties involved in the measure above at the moment but I think it at least works.
I know I've answered similar before. For this case, you need a measure filter like this:
HasAllAttributes =
VAR SelectedAttributes =
CALCULATETABLE ( VALUES ( Attributes[ATTRIBUTE] ), ALLSELECTED ( Attributes ) )
VAR AttributesInContext =
VALUES ( Attributes[ATTRIBUTE] )
VAR SelectedInContext =
EXCEPT ( SelectedAttributes, AttributesInContext )
RETURN
IF ( ISEMPTY ( SelectedInContext ), 1, 0 )
This is assuming you have a slicer on Attributes[ATTRIBUTE] where the relationship doesn't filter UPC Details (the relationship goes the other way in my model).
Thanks for getting back to me so quickly Alexis. Your code makes sense but there is a twist to the data that needs to be shown in the crosstab. Essentially I want to see a list of all the attributes associated with those UPC's that meet the bundled attribute criteria along with their dollar sales (business knows that the numbers will repeat). Here's what I am hoping to have happen (step 2 is for display purposed only to show what should be happening behind the scenes):
This is tricky. You don't want the attribute slicer filtering the attribute list directly but indirectly through the UPC... not simple.
In order to do this, you have to have a new table since the existing slicer column can't be used in the table.
I created DimAttributes table of unique attributes simply defined as
DimAttributes = VALUES ( Attributes[ATTRIBUTE] )
and set up the relationships like this:
Then I defined a new measure and put it on a table with DimAttribute[ATTRIBUTE].
Attribute Sales =
VAR ValidUPCs =
FILTER (
VALUES ( UPC[UPC] ),
CALCULATE ( [HasAllAttributes], ALL ( DimAttributes ) ) = 1
)
RETURN
CALCULATE (
SUM ( Dollars[Dollar Sales] ),
REMOVEFILTERS ( Attributes[ATTRIBUTE] ),
CROSSFILTER ( Attributes[UPC], UPC[UPC], BOTH ),
KEEPFILTERS ( UPC[UPC] IN ValidUPCs )
)
I don't have the energy to explain all of the subtleties involved in the measure above at the moment but I think it at least works.
Awesome Alexis - was able to get your solution to work and thanks again for your help!
I tried something like this and it appears to work correctly as long as the actual UPC value is in the report but now when it is aggregated:
I have not tested this - but it might work to use two slicers. Put the same field in each slicer - the Attribute. Each slicer limits what is shown on the page, so if you pick Antibiotics Free from one, and Kosher from the other, you get only items that are both Kosher and Antibiotics free.
Yeah, I considered this and it's not a bad idea. I'm pretty sure you could get it to work but it doesn't scale very well if you need more than two attributes and take more space and effort to use.
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 |
---|---|
113 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |