Iterate over two different columns/tables

[Silvard]

Hi,

The below measure is mostly producing the correct result, but given SQLBI recommend not to create calculated columns within summarize and instead use addcolumns to do so, I'm looking for a better solution.

Ive already tried their recommendation but unfortunately it creates the same max created date value across all rows and the iteration fails severely.

Essentially, I want to work out the date difference between the Created Date and the Assessment End within the context of current dates/period (eg, a line chart showing years)

These two columns do not share the same key and are both connected to the fact table.

Your help and input is much appreciated!

Time = 
VAR FilteredAssessments = CALCULATETABLE(Assessments,Assessments[Assessment Step - Name] = "Condition1" && MAX(Assessments[End]))

	VAR VirtualTable = 
	SUMMARIZE(FilteredAssessments,Assessments[Assessment Step ID],Assessments[End],"Created",CALCULATE(TRUNC(MAX(Jobs[Created])),TREATAS(VALUES(Facts[JobID]),Jobs[JobID]),USERELATIONSHIP(Jobs[Created],'Calendar'[Date])))
	
    RETURN
    AVERAGEX(VirtualTable,DATEDIFF([Created],TRUNC(Assessments[End]),DAY)))

[v-yaningy-msft]

Hi, @Silvard 

Based on your description, it's hard to figure out what you expect to achieve. You can share the pbix file without sensitive data and give your desired implementation.

Best Regards,
Yang
Community Support Team

[Silvard]

Hi @v-yaningy-msft 

Thank you for your response.

I would like to get the datedifference between two dates from different tables that are only "connected" via the facts table. The keys of their respective tables are not at the same granularity and as such can't be connected to each other.

The measure will be used in a line chart detailing time period and should respond accordingly.

[v-yaningy-msft]

Hi, @Silvard 

You can describe your needs with specific data, which can help realize your output faster.

Best Regards,
Yang
Community Support Team