Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
95 | |
76 | |
65 | |
51 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |