topic Re: Measure: filter "product name" where sales>0 in DAX Commands and Tips

Measure: filter "product name" where sales>0

jayjay0306 — Wed, 10 Jan 2024 14:51:35 GMT

Hi folks,

I hope you can help me here:

I have a report where I need to add a "product name"-slicer, but I only want to see the product names of products, where sales>0

Example:

if I combine the product- and sales table, I get this:

hence, what I want to see in my filter is this:

My problem is that the relation between the "Product"-table and the "Sales"-table is single "1:many"-relation:

model(part of):

so I can't just add a filter on the "slicer"-visual with the crieteria: "Sales" is greater than 0.

Therefore,

Question:

Is it possible to make a calculated column on the product table which "flags" the products where sales>0?

if "yes", how?

I have tried to make the following calculated column on the product table:

Flag= IF(SUM(Sales)>0,1,0)

but the I only gets "1".

thanks,

Br,

jayjay0306

Re: Measure: filter "product name" where sales>0

lukiz84 — Wed, 10 Jan 2024 15:17:45 GMT

just add sales to the slicer filters and filter to "is not blank"

Re: Measure: filter "product name" where sales>0

jayjay0306 — Wed, 10 Jan 2024 15:32:27 GMT

Hi lukiz84,

Thanks but I am afraid, I have already tried that and for some reason it doesn't work. I has no effect? 😞

Re: Measure: filter "product name" where sales>0

lukiz84 — Wed, 10 Jan 2024 15:37:40 GMT

So the relationship between D_Product and Sales is 1:n and is active? => Good

The measure in Sales is "Sales = SUM(Sales[Sales])? => Good

Then it should work when you place the Product from "D_Product" in the Slicer and set the filter as described.

But it doesn't work apparently so please show some screenshots (relationship, slicer with filter and the measure) - i'm sure we can solve this 😉

Re: Measure: filter "product name" where sales>0

jayjay0306 — Wed, 10 Jan 2024 15:59:22 GMT

Thanks lukiz84, You just gave me a great help there 🙂

You write "the measure in Sales...."! the "measure" I have tried to use is merely the "Value"-column "sales" in the table. If I make it as an actual measure (i.e. SUM(Sales[Sales]), it works!

well, well, I learn every day 😉

thanks for the input.

Br,

Jayjay0306