Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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 select...
Need Help
  • AlexisOlson's avatar
    AlexisOlson
    4 years ago

    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.

AlexisOlson's avatar
AlexisOlson
Icon for Super User rankSuper User
4 years ago

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

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

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

     

    • AlexisOlson's avatar
      AlexisOlson
      Icon for Super User rankSuper User
      4 years ago

      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.

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

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