Forum Discussion

Silvard's avatar
Silvard
Resolver I
1 year ago
Solved

Calling All Experts

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 ye...
  • Silvard's avatar
    Silvard
    1 year ago

    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))

     

     

Silvard's avatar
Silvard
Resolver I
1 year ago

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?

 

Silvard's avatar
Silvard
Resolver I
1 year ago

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))