cancel
Showing results for
Did you mean:
Highlighted
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:

 Store Id_Product Sold Shopping F10 NO Street F10 YES Market F10 NO Shopping F20 NO Street F20 YES Market F20 YES Shopping F30 YES Street F30 YES Market F30 NO

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_Product Measure F10 0 F20 0 F30 1

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

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

Try this MEASURE

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

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

Try this MEASURE

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