Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi Everyone,
I need your help, I have very sample request
I want the formula to check if there any previous "active" orders for the same customer ID that has "Active" status so the final output will be "Not yet" status regardless of any other status
Data does not include any date.
Example :
Customer ID | Order Number | Customer name | Status |
1 | 14456 | Johan | Active |
1 | 14876 | Johan | Closed |
2 | 11222 | Test1 | Active |
2 | 11484 | Test1 | Close |
2 | 11454 | Test1 | Close |
3 | 17461 | Sarah | Close |
Output :
Customer ID | Order Number | Customer name | Status | All customer package deliver |
1 | 14456 | Johan | Active | Not yet |
1 | 14876 | Johan | Closed | Not yet |
2 | 11222 | Test1 | Active | Not yet |
2 | 11484 | Test1 | Close | Not yet |
2 | 11454 | Test1 | Close | Not yet |
3 | 17461 | Sarah | Close | Yes |
Solved! Go to Solution.
All customers package deliver CC =
IF (
"Active"
IN SUMMARIZE (
FILTER ( Data, Data[Customer ID] = EARLIER ( Data[Customer ID] ) ),
Data[Status]
),
"Not yet",
"Yes"
)
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Hi @king2005r
You can use the following for a calculated column
All Customer Package Delivered =
IF (
COUNTROWS (
FILTER (
Data,
Data[Customer ID] = EARLIER ( Data[Customer ID] )
&& Data[Status] = "Active"
)
) > 0,
"Not yet",
"Yes"
)
Hi,
Please check the below picture and the attached pbix file.
It is for creating a calculated measure.
All customers package deliver: =
IF (
HASONEVALUE ( Data[Customer ID] ),
IF (
"Active"
IN CALCULATETABLE (
VALUES ( Data[Status] ),
ALLEXCEPT ( Data, Data[Customer ID] )
),
"Not yet",
"Yes"
)
)
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Hi
Thank you for your reply, the attached solution is measurement, can I have it as a column?
All customers package deliver CC =
IF (
"Active"
IN SUMMARIZE (
FILTER ( Data, Data[Customer ID] = EARLIER ( Data[Customer ID] ) ),
Data[Status]
),
"Not yet",
"Yes"
)
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Thank you so much, this is work fine 🙂 appreciated!
User | Count |
---|---|
57 | |
21 | |
18 | |
16 | |
12 |
User | Count |
---|---|
85 | |
54 | |
39 | |
21 | |
18 |