topic Re: Exclude items from distinct count on higher level of aggregation in DAX Commands and Tips

Exclude items from distinct count on higher level of aggregation

soldous — Tue, 21 Jan 2025 10:00:45 GMT

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

Re: Exclude items from distinct count on higher level of aggregation

Kedar_Pande — Tue, 21 Jan 2025 10:06:56 GMT

soldous

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

Re: Exclude items from distinct count on higher level of aggregation

soldous — Tue, 21 Jan 2025 10:28:01 GMT

Kedar_Pande

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

Re: Exclude items from distinct count on higher level of aggregation

johnt75 — Tue, 21 Jan 2025 11:00:23 GMT

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

Re: Exclude items from distinct count on higher level of aggregation

soldous — Tue, 21 Jan 2025 10:58:52 GMT

johnt75

what

&& SELECTEDVALUE ( F_OrderItem[order_key] ) = 1

should mean please? Order key is string data type so your measure returns error

Re: Exclude items from distinct count on higher level of aggregation

johnt75 — Tue, 21 Jan 2025 11:01:03 GMT

My mistake, that should have been lost_flag, not order_key. I have edited my original post to reflect that.

Re: Exclude items from distinct count on higher level of aggregation

soldous — Tue, 21 Jan 2025 11:16:42 GMT

johnt75

still doesnt work 😕

If I select just one order it works as expected:

but with all selected you can see that for 3.1. it still returns 1 correctly but that 1 is not propagated to the higher level but should be:

Re: Exclude items from distinct count on higher level of aggregation

johnt75 — Tue, 21 Jan 2025 11:21:15 GMT

A couple of things here. The values of 0 on 15th and 16th should be propagated to the month level, no ?

Also, I had expected that the only values for lost_flag would be 0 or 1. What values could that column contain ?

Re: Exclude items from distinct count on higher level of aggregation

soldous — Tue, 21 Jan 2025 11:32:15 GMT

lost_flag has only two values 0 or 1

the expected result should be:

3.1. - 1

11.1. - 1

13.1. - 1

14.1. - 2 but here the order 16031 is not lost anymore for 15th - should not be counted on monthly level

15.1. - 1

16.1. - 0

and on 202501 monthly level it should return 5

Re: Exclude items from distinct count on higher level of aggregation

johnt75 — Tue, 21 Jan 2025 13:50:18 GMT

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 )

Re: Exclude items from distinct count on higher level of aggregation

soldous — Tue, 21 Jan 2025 14:11:16 GMT

It works it seems. Thanks a lot!

topic Re: Exclude items from distinct count on higher level of aggregation in DAX Commands and Tips

Exclude items from distinct count on higher level of aggregation

Re: Exclude items from distinct count on higher level of aggregation

Lost Orders = CALCULATE(DISTINCTCOUNT(F_OrderItem[order_key]),FILTER(SUMMARIZE(F_OrderItem,F_OrderItem[order_key],"AllLost", MAX(F_OrderItem[lost_flag])),[AllLost] = 1))

Re: Exclude items from distinct count on higher level of aggregation

Re: Exclude items from distinct count on higher level of aggregation

Re: Exclude items from distinct count on higher level of aggregation

Re: Exclude items from distinct count on higher level of aggregation

Re: Exclude items from distinct count on higher level of aggregation

Re: Exclude items from distinct count on higher level of aggregation

Re: Exclude items from distinct count on higher level of aggregation

Re: Exclude items from distinct count on higher level of aggregation

Re: Exclude items from distinct count on higher level of aggregation

Lost Orders =
CALCULATE(
DISTINCTCOUNT(F_OrderItem[order_key]),
FILTER(
SUMMARIZE(
F_OrderItem,
F_OrderItem[order_key],
"AllLost", MAX(F_OrderItem[lost_flag])
),
[AllLost] = 1
)
)