cancel
Showing results for
Did you mean:
Highlighted
Helper III

## Except with different quantity of itens

I have the following measure:

```Ind_Sold =
VAR StoreSelec = DISTINCT('Aux_Store'[Store])
RETURN
IF(
ISEMPTY(
EXCEPT(
StoreSelec ;
CALCULATETABLE(VALUES(Sales[Store]); Sales[Ind_Sold] = "Never")
)
);
"Never Sold";
"Sold"
)```

I use the previous measure to "say" if some product "Sold" or "Never Sold", if the product "Sold" at least 1 store, I consider it "Sold", if the product don't sold on all stores, I consider it "Never Sold".

The measure is working so far, the problem is:

I have 3 stores, but the product only exists in 2 stores (and didn't sold on this 2 stores), then, the except is 1, then, the measure returns "Sold", but is incorrect, because it didn't sell on his 2 stores.
But the except is 1, not because of sold, because the product don't exist in the store, I need to adapt the measure to verify the product in only existing stores, is it possible?

Example of table Aux_Store:

 Store Shopping Market Street

Example of table Sale:

 Product Store Ind_Sold F10 Shopping Yes F10 Market Never F10 Street Never F20 Shopping Never F20 Market Never F30 Shopping Yes F30 Market Yes F30 Street Never

My problem is with product F20, it don't sold on Shopping and Market, but the except with table Store is 1, because of quantity.
For product F10 the return should be "Sold" and for F30 should be "Sold" too, because they sold in at least 1 store, but F20 should be "Never Sold".

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support

## Re: Except with different quantity of itens

You may this simple measure to get your requirement:

```Measure 3 =
IF (
CALCULATE ( COUNTA ( Sales[Product] ), Sales[Ind_Sold] = "Yes" ) > 0,
"Sold",
"Never Sold"
)```

Best Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Community Support

## Re: Except with different quantity of itens

You may this simple measure to get your requirement:

```Measure 3 =
IF (
CALCULATE ( COUNTA ( Sales[Product] ), Sales[Ind_Sold] = "Yes" ) > 0,
"Sold",
"Never Sold"
)```

Best Regards,

Lin

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

Announcements

#### Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

#### Power Platform Community Conference

Check out the on demand sessions that are available now!

#### Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

#### Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors