Forum Discussion
Resolver IIterate over two different columns/tables
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)))
3 Replies
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
SilvardHi 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.Hi, Silvard
You can describe your needs with specific data, which can help realize your output faster.
Best Regards,
Yang
Community Support Team
