cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
gluizqueiroz Regular Visitor
Regular Visitor

How to make a measure to filter only values that matched with all selected values and follow a rule

I have the following real table:

 

StoreId_ProductSold
ShoppingF10NO
StreetF10YES
MarketF10NO
ShoppingF20NO
StreetF20YES
MarketF20YES
ShoppingF30YES
StreetF30YES
MarketF30NO

 

I have a slicer for Store on my page.

 

I need to make a measure that returns only products with Sold = YES on ALL Stores selected.

 

If user selects "Shopping" and "Street" for example, I need to show on a visual table  the following result:

 

Id_ProductMeasure
F100
F200
F301

 

Like I said, the user selected "Shopping" and "Street" and the measure returns 1 only for Id_Product = F30, because it is the only product that have sold on "Shopping" and "Street". 

Id_Product = F10 returns 0 because it doesn't sold on "Shopping"
Id_Product = F20 returns 0 because it doesn't sold on "Market" and "Shopping"
Id_Product = F20 returns 1 because it sold on "Market" and "Shopping".

I will use that measure to show on table just the values with 1, in other words, just products that have sold on all Stores selected.

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: How to make a measure to filter only values that matched with all selected values and follow a r

@gluizqueiroz 

 

Try this MEASURE

 

Measure =
IF (
    ISEMPTY (
        EXCEPT (
            VALUES ( Table1[Store] ),
            CALCULATETABLE ( VALUES ( Table1[Store] ), Table1[Sold] = "Yes" )
        )
    ),
    1,
    0
)
2 REPLIES 2
Super User
Super User

Re: How to make a measure to filter only values that matched with all selected values and follow a r

@gluizqueiroz 

 

Try this MEASURE

 

Measure =
IF (
    ISEMPTY (
        EXCEPT (
            VALUES ( Table1[Store] ),
            CALCULATETABLE ( VALUES ( Table1[Store] ), Table1[Sold] = "Yes" )
        )
    ),
    1,
    0
)
gluizqueiroz Regular Visitor
Regular Visitor

Re: How to make a measure to filter only values that matched with all selected values and follow a r

Hey @Zubair_Muhammad

I tried your solution and it work correctly. 

Thanks man, you are awesome!

Have a nice day!