Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to Solution.
How about the two measures below:
Quantity = SUM(Quantity) Sum of stocks without future = CALCULATE( [Valid Stock Value]; FILTER( PNid; [Quantity ] = 0))
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)
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:
How about the two measures below:
Quantity = SUM(Quantity) Sum of stocks without future = CALCULATE( [Valid Stock Value]; FILTER( PNid; [Quantity ] = 0))
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
95 | |
76 | |
65 | |
51 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |