Filter out subcategory value not visible in visual

[Petanek333]

Hi,

maybe I'm already overworked and this is probably a very easy problem to solve, but I can't figure it out.

I have one table where there are fields Type and Brand and there is no Product (red). In two other tables the Product field is there.

In all three tables there is a filter that the Stock now measure must be greater than 0. Only for such items do I want to add up the output - measure Sold.

In those tables (green) where Product is in the visual, products with zero stock are omitted (Products E and J).

How can I make it so that even in a table where there is no Product column in the visual, these products are omitted so that the sum of sold is the same as in the other tables, i.e. 35 and not 51?

I have a more complex data model where the sold measure analogy is calculated for a selected time period, so I would need the aggregation by product to be directly in the measure.

Sample file attached: Data 

[Petanek333]

This might be the solution. But I would really appreciate if someone confirmed it is OK to solve it like this or if there is any another better way.

FilteredSales =
VAR virtualtable =
    FILTER (
        SUMMARIZE (
            'Fact table',
            'Dim table'[Product],
            "@Stock", SUM ( 'Fact table'[Stock left] )
        ),
        [@Stock] > 0
    )
RETURN
    SUMX ( virtualtable, [Sold] )

[v-rzhou-msft · Accepted solution]

Hi @Petanek333 ,

Your workaround is a good way, you create a virtual table to remove all products which [Stock left] = 0. If the relationship between Dimtable and Fact table is one to many, you can use this workaround.

In your sample, the relationship is one to one. I think you can try my code, it will be easier.

Measure = CALCULATE(SUM('Fact table'[Out]),FILTER('Fact table','Fact table'[Stock left]<>0))

Result is as below.

Best Regards,
Rico Zhou

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

[Petanek333]

Thank you, it is simple and better 🙂