Creating a flag using a value and two date fields

Question

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.&nbsp;&nbsp;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.&nbsp;Service order #&nbsp; &nbsp;Serial #&nbsp; &nbsp;Contract Start Dt&nbsp; &nbsp;Contract End Dt&nbsp; &nbsp;Miltiple Service Orders in a Contract&nbsp; &nbsp;123ABC1/1/202312/31/2023yes456ABC1/1/202312/31/2023yes789DEF3/31/20233/31/2024no789DEF4/1/20244/1/2025no

johnt75 · Accepted Answer

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 &gt; 1, "Yes", "No" )
RETURN
    Result

Forum Discussion

Creating a flag using a value and two date fields

1 Reply

Related content

Can i use a measure selected in a slicer (created with field parameters) to create a rank?

If field is NULL then use other field

To create a filter using comma separated field.

Create Calculated Fields based on Field Parameter selected

Create a dynamic measures using field parameter measure values

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