Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hi Power Bi community,
I am hoping to gather some help in creating a flag to identify multiple service orders for a serial number based on contract start and end dates. I find it difficult to phrase this question....I belive the below visual shows this better then I can explain.
The first two rows show 2 service orders for the same serial # with the same contract start and end date. The bottom two rows show 2 service orders for the same serial # but from different contract start and end dates. I am hoping to have the column "Miltiple Service Orders in a Contract" using DAX.
Service order # | Serial # | Contract Start Dt | Contract End Dt | Miltiple Service Orders in a Contract |
123 | ABC | 1/1/2023 | 12/31/2023 | yes |
456 | ABC | 1/1/2023 | 12/31/2023 | yes |
789 | DEF | 3/31/2023 | 3/31/2024 | no |
789 | DEF | 4/1/2024 | 4/1/2025 | no |
Solved! Go to Solution.
You can create a calculated column like
Multiple service orders in a contract =
VAR NumOrders =
CALCULATE (
COUNTROWS ( 'Table' ),
ALLEXCEPT (
'Table',
'Table'[Serial #],
'Table'[Contract start date],
'Table'[Contract end date]
)
)
VAR Result =
IF ( NumOrders > 1, "Yes", "No" )
RETURN
Result
You can create a calculated column like
Multiple service orders in a contract =
VAR NumOrders =
CALCULATE (
COUNTROWS ( 'Table' ),
ALLEXCEPT (
'Table',
'Table'[Serial #],
'Table'[Contract start date],
'Table'[Contract end date]
)
)
VAR Result =
IF ( NumOrders > 1, "Yes", "No" )
RETURN
Result
User | Count |
---|---|
22 | |
11 | |
8 | |
6 | |
6 |
User | Count |
---|---|
25 | |
13 | |
11 | |
9 | |
6 |