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 number of customers sold, and value sold, and another table of all customers, including industry, and overall spend (including drinks, food, and more). I've built a relationship between the two tables with the customer name being the link.

 

example table 1 below (removing customer name for ease of illustration) :

Table1

Drinkfirst Customer Name# of total CustomersProfit
TeaCustomer A1010000
CoffeeCustomer B55000
WaterCustomer C22000

 

table2

Customer NameCustomer IndustrySalesperson
Customer AManufacturingBob
Customer B GovernmentDerek
Customer CLegal

Sam

Customer DManufacturing

Bob

Customer EFinance

Bob

 

I've got a measure that calcuates the top industry for a drink using the following formula:

 

 

TopIndustry = 
Var _maxValue =
Maxx(Values(Table2[Customer Industry]),[Rank])
RETURN
CALCULATE(
    Min(Table2[Customer Industry]),
    Filter(Values(Table2[Customer Industry]),[Rank]= _maxValue)
)

 

 

 

Where [Rank] is a multiplication of number of customers by profit. this then successfully tells me, for Tea, the top industry is Manufacturing. 

 

what i'm now trying to do - is build the following table, that using slicers for type of drink and salesperson, that firstly removes any customers who have already bought the drink, (so in this case, Customer A) and returns a true or false against the customer's industry to say if it matches the top industry for that drink - example below:

Drink Selected = Tea

Salesperson Selected = Bob

Table3

Customer NameCustomer IndustryHas Bought DrinkIs In right Industry
Customer AManufacturingYesYes
Customer DManufacturing NoYes
Customer EFinanceNo No
    

 

i'd then filter the table so it removes any 'yes' rows in the 'Has bought Drink' column and only shows 'Yes' in the industry columns - so we can tell Customer D is in the right industry to buy tea.
Issue is, Table 3, the customer name column is subject to the salesperson slicer, but i don't want the top industry to be subject to the slicer, so trying to play about with row context, Maxx and filterexcept doesn't work - additionally the top industry changes with the drink selected, so it needs to be dynamic.

I'm at my wits end - hoping the above makes sense and someone can offer a solution. 

 

TIA

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.

8 Replies

  • MFelix's avatar
    MFelix
    Super User
    2 years ago

    Hi Redacted_VAR ,

     

    For this you need to remove the context filter from the sales person your measure would be similar to this:

     

    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,

     

    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

  • Anonymous's avatar
    Anonymous
    Not applicable
    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.