Forum Discussion

Redacted_VAR's avatar
Redacted_VAR
Helper I
2 years ago
Solved

Complex Matching varying slicer interactions

So I'm really struggling with this one, i'm not even sure it's possible. I've used some dummy data below to illustrate my problem. in essence I have two tables - one describing the drink, including n...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Redacted_VAR 

     

    Thanks for the reply from MFelix .

     

    Redacted_VAR , please try the following method.

     

    Use the fields in Table2 as Salesperson Slicer

     

    Create a new table as Drink slicer

    Drinktable = VALUES(Table1[Drink])

     

    no relationship between these tables

     

    Create two measures as follow

    Has Bought Drink = 
VAR _Drink = CALCULATE(MAX(Table1[Drink]), FILTER(Table1, [first Customer Name] = SELECTEDVALUE(Table2[Customer Name])))
RETURN
IF(_Drink = SELECTEDVALUE(Drinktable[Drink]), "Yes", "No")

     

    Is In right Industry = IF(MAX(Table2[Customer Industry]) = [TopIndustry], "Yes", "No")

     

    Output:

     

    Best Regards,
    Yulia Xu

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Redacted_VAR's avatar
Redacted_VAR
Helper I
2 years ago

Hi MFelix,

 

this looks close, however when I use the above code i get the following:

 

"A function 'FILTER' has been used in a True/False expression that is used as a table filter expression, this is not allowed."

 

i've copied it directly so not sure where's its going wrong.  

  • MFelix's avatar
    MFelix
    Super User
    2 years ago

    Try the following:

     

    TopIndustry =
VAR _maxValue =
    MAXX ( VALUES ( Table2[Customer Industry] ), [Rank] )
RETURN
    CALCULATE (
        MIN ( Table2[Customer Industry] ),
        FILTER ( VALUES ( Table2[Customer Industry] ), [Rank] = _maxValue ),
        REMOVEFILTERS ( Table2[SalesPerson] )
    )
    • Redacted_VAR's avatar
      Redacted_VAR
      Helper I
      2 years ago

      Hi MFelix , 

       

      so that's not throwing up the error anymore, but it's still not working - the measure only returns the industry of the customer, if they HAVE brought the drink, it doesn't return the top industry for that drink

      • MFelix's avatar
        MFelix
        Super User
        2 years ago

        Now I understand try the following update:

         

        TopIndustry =
VAR _maxValue =
    MAXX ( VALUES ( Table2[Customer Industry] ), [Rank] )
RETURN
    CALCULATE (
        MIN ( Table2[Customer Industry] ),
        FILTER ( VALUES ( Table2[Customer Industry] ), [Rank] = _maxValue ),
        REMOVEFILTERS ( Table2[SalesPerson], Table2[Customer Name] )
    )