Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
43 | |
26 | |
20 | |
14 | |
8 |
User | Count |
---|---|
75 | |
50 | |
47 | |
17 | |
17 |