Forum Discussion
Advocate IIExclude items from distinct count on higher level of aggregation
Hello,
I have a measure
CALCULATE( DISTINCTCOUNT(F_OrderItem[order_key]), F_OrderItem[lost_flag] = 1 )to calculate orders that are lost - not part of the sale. But it can be lost one day but successfully sold next day. On a daily level it works fine but on a monthly / city / ... level I would need to count only those that still have lost flag = 1 on all occurrences for that aggregation level. With other words, if the order has a lost flag = 1 in one day but = 0 the next day it should be counted as lost for 1st day but for the whole month not anymore.
could you help me modify the measure?
Thanks a lot
OK, I think this works
Num Lost = SUMX ( DISTINCT ( F_OrderItem[order_key] ), VAR Flags = CALCULATETABLE( VALUES ( F_OrderItem[lost_flag] ) ) VAR Result = IF ( COUNTROWS ( Flags ) = 1 && CALCULATE( SELECTEDVALUE ( F_OrderItem[lost_flag] ) ) = 1, 1, 0 ) RETURN Result )
10 Replies
- Kedar_Pande
Super User
Lost Orders =
CALCULATE(
DISTINCTCOUNT(F_OrderItem[order_key]),
FILTER(
SUMMARIZE(
F_OrderItem,
F_OrderItem[order_key],
"AllLost", MAX(F_OrderItem[lost_flag])
),
[AllLost] = 1
)
)💡 If this helped, please give Kudos 👍 or mark it as a Solution ✅.
Best regards,
Kedar
🌐 Connect on LinkedIn
soldousstill the same:
I have selected just one item. "Lost" is original measure, "measure" is the new one. In month total it should return 0 because for 15.1. the order is not lost anymore.
johnt75I think you can use
Num Lost = SUMX ( DISTINCT ( F_OrderItem[order_key] ), VAR Flags = VALUES ( F_OrderItem[lost_flag] ) VAR Result = IF ( COUNTROWS ( Flags ) = 1 && SELECTEDVALUE ( F_OrderItem[lost_flag] ) = 1, 1, 0 ) RETURN Result )
