topic Re: Calling All Experts in DAX Commands and Tips

Calling All Experts

Silvard — Wed, 18 Dec 2024 12:27:41 GMT

I’m having issues with the below measure.

It’s showing a straight line in a line graph using year as x axis and the value doesn’t change when applying external filters. If I change to specific years, the value changes but the straight line remains.

The assessment and jobs tables are connected to the facts table and the two date fields being used, share inactive relationships with the calendar date field.

Ultimately, I just need the average time between two dates from different columns, but one that is responsive to time and other filters.

Unfortunately I’m unable to share any data and I greatly appreciate your assistance.

AverageTime =

VAR FilteredAssessmentsTable =

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

VAR VirtualTable =

ADDCOLUMNS(FilteredAssessments,"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

Re: Calling All Experts

DataNinja777 — Wed, 18 Dec 2024 13:07:30 GMT

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,

Re: Calling All Experts

Silvard — Thu, 19 Dec 2024 01:17:32 GMT

Thanks for continuing to help DataNinja777

I have now managed to get curves on the line and different years show different results using the updated measure below.

Average time =

VAR FilteredAssessments = CALCULATETABLE(SELECTCOLUMNS(FILTER(Assessments,Assessments[Assessment Step - Name] = "Documents","AssessmentID",Assessments[Assessment Step ID],"EndDate",Assessments[Assessment Step - End]),USERELATIONSHIP(Assessments[Assessment Step - End],'Calendar'[Date]))

VAR VirtualTable = CALCULATETABLE(ADDCOLUMNS(FilteredAssessments,"JobCreatedDate",LOOKUPVALUE(Jobs[Job Created Date],Jobs[JobID],LOOKUPVALUE(Facts[JobID],Facts[Assessment Step ID],[AssessmentID]))),USERELATIONSHIP(Jobs[Job Created Date],'Calendar'[Date]))

VAR AverageDays =

AVERAGEX(VirtualTable,DATEDIFF([JobCreatedDate],[EndDate],DAY))

RETURN

AverageDays

The graph/numbers/results are however still unresponsive to filters from outside.

What would you suggest?

Re: Calling All Experts

Silvard — Thu, 19 Dec 2024 04:07:56 GMT

I managed to solve the issue by using the below. Essentially, using treatas was the missing piece.

Average time =

VAR FilteredAssessments = FILTER(Assessments,Assessments[Assessment Step - Name] = "Documents")

VAR VirtualTable =

SUMMARIZE(FilteredAssessments,Assessments[Assessment Step ID],Assessments[Assessment Step - End],"JobCreatedDate",CALCULATE(TRUNC(MAX(Jobs[Job Created Date])),Jobs[Job Created Date] <> BLANK(),TREATAS(VALUES(Facts[JobID]),Jobs[JobID]),TREATAS(VALUES(Assessments[Assessment Step ID]),Facts[Assessment Step ID]),USERELATIONSHIP(Jobs[Job Created Date],'Calendar'[Date])))

RETURN

AVERAGEX(VirtualTable,DATEDIFF([JobCreatedDate],TRUNC([Assessment Step - End]),DAY))