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.
Hello Everyone,
I have two tables
Table 1:-
Voucher No. | Invoice No. | Invoice Amount | Invoice Date | Vendor Name |
1 | ABC-1 | 100 | 14/05/2019 | ABC Company |
1 | ABC-2 | 200 | 14/05/2019 | ABC Company |
2 | EFG-1 | 300 | 31/05/2019 | EFG Company |
2 | EFG-2 | 400 | 31/05/2019 | EFG Company |
3 | XYZ-1 | 500 | 30/06/2019 | XYZ Company |
3 | XYZ-2 | 600 | 30/06/2019 | XYZ Company |
Table No.2:-
Invoice No |
ABC-1 |
ABC-2 |
EFG-1 |
EFG-2 |
XYZ-1 |
XYZ-2 |
Now i am looking for a DAX formula in table no.2 which would return the details like vendor name, Invoice Date, Voucher No. etc.
I dont want any power query solution because that would increase my memory. I am looking for need DAX measures which would return the results using the relationship between two tables.
I hope i explained my query properly.
Thank you so much advance.
Best Regards,
Shib
Solved! Go to Solution.
Hi @coolshib ,
Try this measure
Vendor Invoice Date Related = CALCULATE(LASTNONBLANK('Table No1'[Invoice Date],1), FILTER('Table No2','Table No2'[Invoice No] = MAX('Table No1'[Invoice No.])))
Vendor Name Related = CALCULATE(LASTNONBLANK('Table No1'[Vendor Name],1), FILTER('Table No2','Table No2'[Invoice No] = MAX('Table No1'[Invoice No.])))
Incase you are looking for a Calculated Column
Column = RELATED('Table No1'[Vendor Name])
Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)
Hi @coolshib ,
Try this measure
Vendor Invoice Date Related = CALCULATE(LASTNONBLANK('Table No1'[Invoice Date],1), FILTER('Table No2','Table No2'[Invoice No] = MAX('Table No1'[Invoice No.])))
Vendor Name Related = CALCULATE(LASTNONBLANK('Table No1'[Vendor Name],1), FILTER('Table No2','Table No2'[Invoice No] = MAX('Table No1'[Invoice No.])))
Incase you are looking for a Calculated Column
Column = RELATED('Table No1'[Vendor Name])
Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)
Thank you so much @harshnathani ...
The DAX measures give me the results which i eaxctly wanted.
Many thanks.
Best Regards,
@coolshib - If you just create a relationship between the tables then you can display all of the information in a visual, there is no need to write DAX or create calculated columns.
If Table 1 is on the 1 side of a relationship on Invoice No, you can just use the RELATED function
VendorName = RELATED(Table1[Vendor Name])
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
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 |
---|---|
43 | |
21 | |
20 | |
15 | |
13 |
User | Count |
---|---|
45 | |
41 | |
39 | |
19 | |
19 |