topic Re: Exclude whole group if value is not found in DAX Commands and Tips

Exclude whole group if value is not found

JayV — Wed, 22 Feb 2023 19:59:34 GMT

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:

ID	Store	Product
1	1	Chips
2	1	Paint
3	2	Paint
4	3	Soda
5	3	Chips

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

ID	Store	Product
1	1	Chips
2	1	Paint
3	2	Paint

Thanks a lot!

Re: Exclude whole group if value is not found

Jihwan_Kim — Thu, 23 Feb 2023 04:29:56 GMT

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] ) )

Re: Exclude whole group if value is not found

JayV — Thu, 23 Feb 2023 19:25:47 GMT

Worked perfectly! Thanks a lot Jihwan_Kim !

Re: Exclude whole group if value is not found

JayV — Mon, 07 Aug 2023 19:24:25 GMT

Thank you!