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,
I'm going to start with my original issue and maybe there is a simplier solution.
I'm using this formula to return QtyGood from the previous step:
Previous Step Qty =
IF (
Query2[PrevStepSequenceNum] = 0,
Query2[StepQtyDue],
LOOKUPVALUE (
Query2[QtyGood],
Query2[OrderNum], Query2[OrderNum],
Query2[OrderStepSequenceNum], Query2[PrevStepSequenceNum],0
)
)
It works great unless there are two values returned as you can see in this picture:
How can I have the alternate value for the Lookup to return the sum of these rows(1710 + 6220 = 7930)?
My first thought was to make a custom column that would sum the QtyGood based on the OrderNum and OrderStepSeqNum columns matching. But I don't know how to do this?
Is there an easier way, if not how can I create this custom column?
Thank you,
-Justair07
Solved! Go to Solution.
Hi @Justair07 ,
It seems like you have set a static value as the result when lookupvalue not work, I'd like to suggest you use sum(Query2[QtyGood]) to return summary result.
Previous Step Qty =
IF (
Query2[PrevStepSequenceNum] = 0,
Query2[StepQtyDue],
LOOKUPVALUE (
Query2[QtyGood],
Query2[OrderNum], Query2[OrderNum],
Query2[OrderStepSequenceNum], Query2[PrevStepSequenceNum],
SUM ( Query2[QtyGood] )
)
)
Regards,
Xiaoxin Sheng
Hi @Justair07 ,
It seems like you have set a static value as the result when lookupvalue not work, I'd like to suggest you use sum(Query2[QtyGood]) to return summary result.
Previous Step Qty =
IF (
Query2[PrevStepSequenceNum] = 0,
Query2[StepQtyDue],
LOOKUPVALUE (
Query2[QtyGood],
Query2[OrderNum], Query2[OrderNum],
Query2[OrderStepSequenceNum], Query2[PrevStepSequenceNum],
SUM ( Query2[QtyGood] )
)
)
Regards,
Xiaoxin Sheng
@v-shex-msft I can't belive it was that simple. I didn't think that alternate value still took into consideration the previous conditions. thankyou so much!
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 |
---|---|
112 | |
97 | |
84 | |
67 | |
60 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |