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
harshagraj
Post Partisan
Post Partisan

Need help to optimize the below dax query for better performance

Days to Design Freeze =
VAR a =
    IF (
        CONTAINS ( tep_projects, tep_projects[DF FY], "FY" ),
        IF (
            CONTAINS ( tep_projects, 'tep_projects'[Design Freeze Status], "Completed" ),
            "Done",
            DATEDIFF (
                MIN ( tep_projects[snap_date] ),
                MAX ( tep_projects[Planned Design Freeze] ),
                DAY
            )
        ),
        "Past"
    )
RETURN
    IF (
        CONTAINS ( tep_projects, tep_projects[Design Freeze Status], "Blank" ),
        BLANK (),
        a
    )
As per the query it is taking too much time

// DAX Query
DEFINE
VAR __DS0Core =
SUMMARIZECOLUMNS(
'tep_projects'[tep_assignments.resource_name],
'tep_projects'[snap_date],
"CountRows", 'tep_projects'[CountRows],
"DF_", 'tep_projects'[DF%],
"CS_", 'tep_projects'[CS%],
"PPAP_", 'tep_projects'[PPAP%]
)

VAR __DS0PrimaryWindowed =
TOPN(
101,
SUMMARIZE(__DS0Core, 'tep_projects'[tep_assignments.resource_name]),
'tep_projects'[tep_assignments.resource_name],
1
)

VAR __DS0SecondaryBase =
SUMMARIZE(__DS0Core, 'tep_projects'[snap_date])

VAR __DS0Secondary =
TOPN(102, __DS0SecondaryBase, 'tep_projects'[snap_date], 1)

VAR __DS0BodyLimited =
NATURALLEFTOUTERJOIN(
__DS0PrimaryWindowed,
SUBSTITUTEWITHINDEX(
__DS0Core,
"ColumnIndex",
__DS0Secondary,
'tep_projects'[snap_date],
ASC
)
)

EVALUATE
__DS0Secondary

ORDER BY
'tep_projects'[snap_date]

EVALUATE
__DS0BodyLimited

ORDER BY
'tep_projects'[tep_assignments.resource_name], [ColumnIndex]



0 REPLIES 0

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.