Calling All Experts

Hi Silvard ,

The issue you're experiencing likely stems from the inactive relationships and the way your measure applies filters. Since you're using USERELATIONSHIP, it is important to ensure that all applied filters interact properly with the relationships and the measure.

The inactive relationships between Jobs and Calendar, as well as Assessments and Calendar, are handled correctly using USERELATIONSHIP in your measure. However, external filters need to propagate properly, which may not be happening effectively in the current configuration. Your FilteredAssessmentsTable must respond to slicers and filters as expected. If it does not, consider explicitly applying context using CALCULATETABLE.

The nested LOOKUPVALUE statements need to be carefully examined to ensure they are returning correct matches. Mismatched or missing data in these lookups could result in calculation errors or incomplete results. Furthermore, filter context from external slicers or visuals must propagate correctly to your measure. Wrapping your logic in CALCULATE can help achieve this.

Here is a refined version of your AverageTime measure:

AverageTime =
VAR FilteredAssessmentsTable =
    SELECTCOLUMNS(
        FILTER(
            Assessments,
            Assessments[Assessment Step] = "Documents"
        ),
        "AssessmentID", Assessments[Assessment Step ID],
        "EndDate", Assessments[Assessment Step - End]
    )

VAR VirtualTable =
    ADDCOLUMNS(
        FilteredAssessmentsTable,
        "JobCreatedDate",
        LOOKUPVALUE(
            Jobs[Job Created Date],
            Jobs[JobID],
            LOOKUPVALUE(
                Facts[JobID],
                Facts[Assessment Step ID], [Assessment Step ID]
            )
        )
    )

VAR AverageDays =
    AVERAGEX(
        CALCULATETABLE(
            VirtualTable,
            USERELATIONSHIP(Jobs[Job Created Date], 'Calendar'[Date]),
            USERELATIONSHIP(Assessments[Assessment Step - End], 'Calendar'[Date])
        ),
        DATEDIFF([JobCreatedDate], [EndDate], DAY)
    )

RETURN
    AverageDays

To troubleshoot, test the virtual table by creating a measure to count rows or inspect it using SUMMARIZE. This will help ensure that the virtual table is being filtered correctly. Additionally, break down the measure into smaller parts and test them individually to identify where the filter context might not be applied. Review the relationships in your model to confirm that the inactive relationships are properly configured and match the fields used in the USERELATIONSHIP function.

If the measure still produces a straight line, it may indicate missing or invalid data in the JobCreatedDate or EndDate fields. Explicitly applying filters with CALCULATE can simulate slicer interactions and verify the measure's responsiveness. Finally, ensure that the Calendar table is correctly linked and not inadvertently filtered by inactive relationships. If possible, replacing LOOKUPVALUE with joins in Power Query may simplify debugging and improve performance. If the issue persists, inspect the data more thoroughly to ensure it aligns with the intended logic.

Best regards,