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

Slicer AND Logic Within Same Product

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:

  1. UPC Dollars - Unique list of UPC’s along with their dollar sales 
  2. UPC Details - Unique list of UPC’s along with their primary dimension values
  3. UPC Attributes - List of UPC’s along with all of the UPC’s associated attributes (multiple values of each UPC)
  4. Attribute Cohort - A mirror of the UPC Attributes table.  Am trying to use this to implement my bundled attribute logic

jbloomfield_0-1636658016852.png

 

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.

 

jbloomfield_1-1636658016852.png

 

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.

jbloomfield_2-1636658016852.png

 

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

UPCProductDepartmentCategory
2531777530JOHNS REFRIGERATED BEEF HOTDOG 6 CT 10 OZDEPT-REFRIGERATEDFRANKFURTERS
61076486381PEACH MANGO LIQUID ENERGY DRINK CAN 16 OZDEPT-BEVERAGESENERGY DRINKS
7453490164FROZEN ICE POP NOVELTY ASSORTED FLAVOR AND SCENT 18 CT 42.3 OZDEPT-FROZENNOVELTIES - FZ
1394100493REFRIGERATED REGULAR CHICKEN ASSORTED FORM 80 OZDEPT-FROZENPOULTRY - FZ/RFG

 

UPC Attributes

UPCATTRIBUTEATTRIBUTE_TYPE
2531777530All Gluten Free Claims/CertsAllergens/Intolerances (Free-from)
2531777530Humane CertifiedAnimal Welfare
7453490164KosherHealth Conscious/Lifestyle Diets
1394100493Humane CertifiedAnimal Welfare
7453490164Contains Artificial FlavorWatch Out Claim: Ingredient 
1394100493Non GMO Project VerifiedClean Label/Minimally Processed
61076486381Contains Caffeine (On-Package)Watch Out Claim: Ingredient 
2531777530Whole 30Health Conscious/Lifestyle Diets
1394100493Humane ClaimAnimal Welfare
2531777530Non GMO Project VerifiedClean Label/Minimally Processed
2531777530All Gluten Free Claims/CertsHealth Conscious/Lifestyle Diets
1394100493Antibiotics FreeAnimal Welfare
61076486381Contains Natural FlavorClean Label/Minimally Processed
2531777530100% Grass FedAnimal Welfare
61076486381Brain HealthAdvantaged Ingredient/Wellness Benefit Claim
2531777530Preservative FreeClean Label/Minimally Processed
2531777530Humane ClaimAnimal Welfare
7453490164Allergen FriendlyAllergens/Intolerances (Free-from)
7453490164Contains Natural FlavorClean Label/Minimally Processed
2531777530Antibiotics FreeAnimal Welfare
1394100493All NaturalClean Label/Minimally Processed
1394100493Hormone FreeAnimal Welfare
2531777530Pasture RaisedAnimal Welfare
2531777530Global Animal PartnershipAnimal Welfare
61076486381"'""Energy""'"Advantaged Ingredient/Wellness Benefit Claim
2531777530Casein FreeAllergens/Intolerances (Free-from)
61076486381Artificial Color FreeClean Label/Minimally Processed

 

UPC Dollars

UPCDollar Sales
1394100493210000
6107648638110500000
253177753014300000
74534901644400000

 

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

1 ACCEPTED 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:

AlexisOlson_0-1636671412282.png

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 )
    )

AlexisOlson_1-1636671598665.png

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.

View solution in original post

7 REPLIES 7
AlexisOlson
Super User
Super User

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).

 

AlexisOlson_0-1636661121381.png

Anonymous
Not applicable

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):

Screen Shot 2021-11-11 at 3.29.42 PM.png

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:

AlexisOlson_0-1636671412282.png

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 )
    )

AlexisOlson_1-1636671598665.png

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.

Anonymous
Not applicable

Awesome Alexis - was able to get your solution to work and thanks again for your help!

Anonymous
Not applicable

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:

UPC Filter =
VAR Selected = COUNTROWS(ALLSELECTED('Attribute Cohort'[Attribute]))
VAR Match = DISTINCTCOUNT('Attribute Cohort'[Attribute])
RETURN
IF(NOT ISFILTERED('Attribute Cohort'[Attribute]), 1, IF(Selected=Match,1))
viviank
Resolver I
Resolver I

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.

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.