Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Petanek333
Helper III
Helper III

Filter out subcategory value not visible in visual

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.

Petanek333_0-1673092227135.png

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 

 

1 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.

RicoZhou_0-1673245951258.png

 

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.

 

View solution in original post

3 REPLIES 3
Petanek333
Helper III
Helper III

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

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.

RicoZhou_0-1673245951258.png

 

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.

 

Thank you, it is simple and better 🙂

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.