Create measure based on another filter - "IF(MAX(...) in ALLSELECTED(...), TRUE, FALSE) not working

[Anonymous]

Hi all,

I have a table that looks like this

And I have created 2 tables to create 2 independent filters.

FruitsList = SUMMARIZE(Fruits, Fruits[Fruits])

SellerList = SUMMARIZE(Fruits, Fruits[Seller])

I have created 2 measures

 

IsFruit = IF(max(Fruits[Fruits]) in ALLSELECTED(FruitsList[Fruits]),1,0)

IsSeller = IF(max(Fruits[Seller]) in ALLSELECTED(SellerList[Seller]),1,0)

I noticed that IsSeller is not working correctly when I choose "A" as the Seller.

Apple, Banana, Pineapple should show up as 1 under "IsSeller" column but only "Banana" is showing up as 1.

Can you help me understand why and how should I fix the measure? Thank you!

[v-chenwuz-msft · Accepted solution]

Hi @Anonymous ,

Why not working correctly:

The reason why incorrectly is the MAX() function.When you choose Slicer "A", The result of Seller table filtered by the slicer is one row {"A"} (as the following), and also the result of ALLSELECTED(SellerList[Seller]).

But for the first row of Matrix visual, Apple, it has tow rows in its filter context.

This contributes the result of max(Fruits[Seller]) is "C", so "C" is not in {"A"}. result of if is 0.

Workaround:

We can try another algorithm to implement the retrieval of whether fruit exists in the table of choices from the slicerlist. Try the code below.

IsSeller =
VAR _fruit =
    COUNTROWS( VALUES( Fruits[Seller] ) )
VAR _except =
    COUNTROWS(
        EXCEPT( VALUES( Fruits[Seller] ), ALLSELECTED( SellerList[Seller] ) )
    )
VAR _r = _fruit - _except
RETURN
    IF( _r > 0, 1, 0 )

result:

Pbix in the end you can refer.

Best Regards

Community Support Team _ chenwu zhu

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

[Samarth_18]

Hi @Anonymous ,

Create a measure with below code:-

isSeller = 
var selected_value = VALUES(SellerList[Seller])
return IF(MAX(Fruits[Seller]) in selected_value,1,0)

isFruit = 
var selected_value = VALUES(FruitsList[Fruits])
return IF(max(Fruits[Fruits]) in selected_value ,1,0)

Output:-

Thanks,

Samarth

[VahidDM]

Hi @Anonymous 

Make sure there is no relationship between your new tables and main table.

Use Values rather than SUMMARIZE.

FruitsList = VALUES ( Fruits[Fruits] )

SellerList = VALUES ( Fruits[Seller] )
then

IsFruit = IF(max(Fruits[Fruits]) in VALUES ( Fruits[Fruits] ),1,0)

IsSeller = IF(max(Fruits[Seller]) in VALUES ( Fruits[Seller] ),1,0)

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/

[Anonymous]

Hi, I followed the instructions but unfortunately it is not working