cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper III
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:

ProductStoreInd_Sold
F10ShoppingYes
F10MarketNever
F10StreetNever
F20ShoppingNever
F20MarketNever
F30ShoppingYes
F30MarketYes
F30StreetNever

 

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
Community Support

Re: Except with different quantity of itens

hi, @gluizqueiroz 

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.

View solution in original post

1 REPLY 1
Highlighted
Community Support
Community Support

Re: Except with different quantity of itens

hi, @gluizqueiroz 

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.

View solution in original post

Helpful resources

Announcements
Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

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

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Tech Marathon

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