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 need your help to match the data from my table as
Equals
Is it possible to create a measure to get the above results?
Result
Type | Order No | Order Date | Delivery Date | Result |
1 | 8767879 | 10-Jan-22 | 22-Jan-22 | Matched |
2 | 8767879 | 10-Jan-22 | 22-Jan-22 | Matched |
1 | 987190 | 12-Jan-22 | 24-Jan-22 | Matched |
2 | 987190 | 12-Jan-22 | 24-Jan-22 | Matched |
1 | 187979 | 12-Jan-22 | 24-Jan-22 | Missing |
1 | 12657 | 20-Feb-22 | 04-Mar-22 | Unmatched |
2 | 12657 | 20-Feb-23 | 04-Mar-23 | Unmatched |
1 | 3121 | 19-Jun-22 | 29-Jun-22 | Unmatched |
2 | 3121 | 19-Jun-22 | 30-Jun-22 | Unmatched |
Thanks
Solved! Go to Solution.
Hi @gauravnarchal , try this calculate column:
Result = var order_p=CALCULATE(count(Table1[Order No]),ALLEXCEPT(Table1,Table1[Order No]))
var order_date_p=CALCULATE(DISTINCTCOUNT(Table1[Order Date]),ALLEXCEPT(Table1,Table1[Order No]))
var delivery_date_p=CALCULATE(DISTINCTCOUNT(Table1[Delivery Date]),ALLEXCEPT(Table1,Table1[Order No]))
return
SWITCH(
TRUE(),
order_p=1, "Missing",
order_p=2 && order_date_p=1 && delivery_date_p=1, "Matched",
"Unmatched"
)
The result:
Best regards
Hi @gauravnarchal , try this calculate column:
Result = var order_p=CALCULATE(count(Table1[Order No]),ALLEXCEPT(Table1,Table1[Order No]))
var order_date_p=CALCULATE(DISTINCTCOUNT(Table1[Order Date]),ALLEXCEPT(Table1,Table1[Order No]))
var delivery_date_p=CALCULATE(DISTINCTCOUNT(Table1[Delivery Date]),ALLEXCEPT(Table1,Table1[Order No]))
return
SWITCH(
TRUE(),
order_p=1, "Missing",
order_p=2 && order_date_p=1 && delivery_date_p=1, "Matched",
"Unmatched"
)
The result:
Best regards
Hi @Bifinity_75 - The solution provided by you did work. There is a small change that is needed to the DAX.
I want to count the order no of each order serial, so have to change the below part
The reason I would need to count the order no of each order serial is that the order numbers are getting duplicated in my data so have to narrow down the count based on the order serial.
I have attached the updated PBIX which is having the order serial. Click Here
Thank you for your help.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
72 | |
70 | |
57 | |
37 | |
36 |
User | Count |
---|---|
85 | |
65 | |
60 | |
46 | |
45 |