Join us for an expert-led overview of the tools and concepts you'll need to pass exam PL-300. The first session starts on June 11th. See you there!
Get registeredPower BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.
Problem to use DATEDIFF function between two dates which located in two but not directly related tables.
Task: Stock.Registration_Date - Invoices.InvoiceDate
Formula: NeedDate= DATEDIFF([Registration_Date],RELATED(Invoices[InvoiceDate]),WEEK)
Err: The column 'Invoices[InvoiceDate]' either doesn't exist or doesn't have a relationship to any table available in the current context
Comments: There two tables "Stock" and "Invoice" - they are related through table in between "InvoiceLines".
Thank you
Solved! Go to Solution.
Given the relationship, you may use RELATEDTABLE Function.
MAXX ( RELATEDTABLE ( Invoices ), Invoices[InvoiceDate] )
For now "Stock" table.. - where i am trying to create new Measure. (In the future this New Measure should be used in
report(Visualization)
but i mentioned those three tbls because tbl InvoiceLines is in between two Main tbls "Stock" and "Invoices"
I need one field from Stock and one from Invoices (they are not linked directly - but only through InvoiceLines )
tnx
Angelika
Given the relationship, you may use RELATEDTABLE Function.
MAXX ( RELATEDTABLE ( Invoices ), Invoices[InvoiceDate] )
Sam,
i think it solved my issue:
Weeks in stock = DATEDIFF([Registration_Date],MAXX(RELATEDTABLE(Invoices),Invoices[InvoiceDate]),WEEK)
I would never thought that MAXX can act as a little liason between "unrelated" tables
Thank you much
Angelika
User | Count |
---|---|
85 | |
79 | |
64 | |
52 | |
46 |
User | Count |
---|---|
101 | |
44 | |
41 | |
39 | |
36 |