Forum Discussion

JayV's avatar
JayV
Icon for Helper I rankHelper I
3 years ago
Solved

Exclude whole group if value is not found

Hi
My problem is similar to this one, but excluding values when not found.
I have a "Store" column and a "Product" column and i want to show all products from the store only when it has the product "Paint", if a store doesn't have "Paint" in one of its rows i need a filter that excludes all of the store in all pages of my model.
If this is my Data:

IDStoreProduct
11Chips
21Paint
32Paint
43Soda
53Chips

I want my model to work only with this rows (Store 1 and 2):

IDStoreProduct
11Chips
21Paint
32Paint


Thanks a lot!

calculated column
exclude
Exists
Filter
Intersection
measure
  • Jihwan_Kim's avatar
    Jihwan_Kim
    3 years ago

    Hi,

    Please check the below picture and the attached pbix file.

    It is for creating a new table.

     

     

     

    Expected result calculated table =
VAR _storewithpaint =
    SUMMARIZE ( FILTER ( Data, Data[Product] = "Paint" ), Data[Store] )
RETURN
    CALCULATETABLE ( Data, TREATAS ( _storewithpaint, Data[Store] ) )

3 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Icon for Super User rankSuper User
    3 years ago

    Hi,

    Please check the below picture and the attached pbix file.

    It is for creating a new table.

     

     

     

    Expected result calculated table =
VAR _storewithpaint =
    SUMMARIZE ( FILTER ( Data, Data[Product] = "Paint" ), Data[Store] )
RETURN
    CALCULATETABLE ( Data, TREATAS ( _storewithpaint, Data[Store] ) )