Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Ben_jamin
Frequent Visitor

Creating a flag using a value and two date fields

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   
123ABC1/1/202312/31/2023yes
456ABC1/1/202312/31/2023yes
789DEF3/31/20233/31/2024no
789DEF4/1/20244/1/2025no
1 ACCEPTED SOLUTION
johnt75
Super User
Super User

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

View solution in original post

1 REPLY 1
johnt75
Super User
Super User

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

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.