Complex Matching varying slicer interactions

[Redacted_VAR]

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

Drink	first Customer Name	# of total Customers	Profit
Tea	Customer A	10	10000
Coffee	Customer B	5	5000
Water	Customer C	2	2000

table2

Customer Name	Customer Industry	Salesperson
Customer A	Manufacturing	Bob
Customer B	Government	Derek
Customer C	Legal	Sam
Customer D	Manufacturing	Bob
Customer E	Finance	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 Name	Customer Industry	Has Bought Drink	Is In right Industry
Customer A	Manufacturing	Yes	Yes
Customer D	Manufacturing	No	Yes
Customer E	Finance	No	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

[Anonymous · Accepted solution]

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]

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]

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]

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]

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

[Redacted_VAR]

Hey,

nope still returns the same as the above - no change from before.

[MFelix]

Hi @Redacted_VAR ,

Without the model it's difficult to give you a correct answer.

Can you please share a mockup data or sample of your PBIX file. You can use a onedrive, google drive, we transfer or similar link to upload your files.

If the information is sensitive please share it trough private message.

[MFelix]

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