Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
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
Solved! Go to 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
)
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
)
what
&& SELECTEDVALUE ( F_OrderItem[order_key] ) = 1
should mean please? Order key is string data type so your measure returns error
My mistake, that should have been lost_flag, not order_key. I have edited my original post to reflect that.
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:
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 ?
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
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
)
It works it seems. Thanks a lot!
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.
User | Count |
---|---|
14 | |
9 | |
7 | |
7 | |
6 |
User | Count |
---|---|
21 | |
11 | |
10 | |
10 | |
8 |