Exclude items from distinct count on higher level of aggregation

[soldous]

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

[johnt75]

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
)

[soldous]

@johnt75 

what 

 && SELECTEDVALUE ( F_OrderItem[order_key] ) = 1

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

[johnt75]

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

[soldous]

@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:

[johnt75]

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 ?

[soldous]

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

[johnt75 · Accepted solution]

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
)

[soldous]

It works it seems. Thanks a lot!

[Kedar_Pande]

@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

[soldous]

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