topic Re: Iterate over two different columns/tables in DAX Commands and Tips

Iterate over two different columns/tables

Silvard — Thu, 20 Feb 2025 23:11:48 GMT

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

Re: Iterate over two different columns/tables

Anonymous — Fri, 21 Feb 2025 06:27:42 GMT

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

Re: Iterate over two different columns/tables

Silvard — Fri, 21 Feb 2025 07:43:14 GMT

Hi Anonymous

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.

Re: Iterate over two different columns/tables

Anonymous — Mon, 24 Feb 2025 02:06:06 GMT

Hi, Silvard

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

Best Regards,
Yang
Community Support Team