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
Anonymous
Not applicable

SUM of values if condition is applied

Hi people!

 

I'm having trouble with the performance of a solution found for this case.

 

In summary, I have a table where my Stock/Inventory is stored for several components. The measure for the Stock Value is Valid Stock Value.

In another table, I have my future requirements for each component (fRequirement). The measure for the values is Requirements.

 

I need to bring the Valid Stock Value for the cases where there are No Requirements. In other words, I need to know how much Inventory there is, where there are no future Requirements.

 

This didn't work at allThis didn't work at allThis worked, but performance was affected harshlyThis worked, but performance was affected harshly

1 ACCEPTED SOLUTION
Anonymous
Not applicable

How about the two measures below:

 

Quantity = SUM(Quantity)
Sum of stocks without future = CALCULATE( [Valid Stock Value]; FILTER( PNid; [Quantity ] = 0))

View solution in original post

5 REPLIES 5
Anonymous
Not applicable

A solution might be to use calculate and avoid the SUMX because it iterates across all rows which can make it quite slow. It would therefore try to filter the table and then just calculating the sum. I hope that you have a dimension table holding information about each component you can have in your inventory, i.e. dComponent. 

 

If you have some relations like below:

dComponent 1:* fStock

dComponent 1:* fRequirements

 

Then the measure below should work:

Sum of stock without future =
CALCULATE( [Valid Stock Value]; RELATED( fRequirements[Quantity]) = 0)

 

 

Anonymous
Not applicable

Hi @Anonymous !

 

They are not directly linked (maybe that's what preventing the CALCULATE to work).

Linking them via PN (Part Number) would be a Many to Many.

What I do is having a table with all DISTINCT PNs (PN ID), as you can see below:

 

relation.png

Anonymous
Not applicable

How about the two measures below:

 

Quantity = SUM(Quantity)
Sum of stocks without future = CALCULATE( [Valid Stock Value]; FILTER( PNid; [Quantity ] = 0))
JosefPrakljacic
Solution Sage
Solution Sage

Hello fpereira89,

 

would it be somehow possible to provide us with sum sample data?

 

Could you try something like this ->

CALCULATE(

                    SUM(WhatEverYouWantoSum),

                    FILTER(Values(fRequiremens[Quantity]), fRequirements[Quantity] = 0)

)

 

I don't think that my answer was helpful - please provide us with some sample data 

 

If it unexpectedly was helpful please mark it as solution and share some kudos. 🙂

 

BR,

Josef

Anonymous
Not applicable

Hi @JosefPrakljacic ,

 

Sorry, but it didn't work.

valid.png

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.