Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
DAX newbie
Hi
I'm trying to update our customer table to indicate if they have ever purchased from a certain dept which is in the invoice table.
Based on the tables below I'm trying to do this
IsBookCustomer = IF invoices exist and invoices.salesdept = 'Books', Yes, No
Also it would be great to report the last invoice date as well
Customers
CustomeID, Name, IsBookCustomer, LastBookInvoiceDate
Invoices
InvoiceDate, SalesDept,InvoiceAmount
Thanks in advance
Tony
Solved! Go to Solution.
Ok, well in that case here is what I suggest:
First, compute the last date of books purchase (if any) with this calculated column (named LastBookInvoiceDate):
= CALCULATE ( MAX ( Invoices[InvoiceDate] ) , Invoices[Sales Dept] = "Books" )
Then, use the column calculated above to check if the customer has ever bought a book:
=IF ( Customers[LastBookInvoiceDate] , "Yes", "No" )
Obviously you need to have a 1 to Many relationship between Customers and Invoices Tables and the calculated columns are both created from the Customers Table.
Feel free to ask any questions if necessary 🙂
Regards,
Tristan Malherbe
Data & BI Consultant at AZEO
Hi @TonyR,
You need to have a relationship (or a link if you prefer) between 'Customers' and 'Invoices' tables to do so. It implies to have a common key (or column) between these 2 tables.
Do you also have CustomerId column in your 'Invoices' Table ?
Hi @Datatouille
My mistake, the tables & fields were simple examples to illustrate the issue. There are common keys linked between the tables, so there is a CustomerID in the Invoice table.
Thx for pointing that out.
Ok, well in that case here is what I suggest:
First, compute the last date of books purchase (if any) with this calculated column (named LastBookInvoiceDate):
= CALCULATE ( MAX ( Invoices[InvoiceDate] ) , Invoices[Sales Dept] = "Books" )
Then, use the column calculated above to check if the customer has ever bought a book:
=IF ( Customers[LastBookInvoiceDate] , "Yes", "No" )
Obviously you need to have a 1 to Many relationship between Customers and Invoices Tables and the calculated columns are both created from the Customers Table.
Feel free to ask any questions if necessary 🙂
Regards,
Tristan Malherbe
Data & BI Consultant at AZEO
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
109 | |
100 | |
95 | |
38 | |
37 |
User | Count |
---|---|
151 | |
126 | |
75 | |
74 | |
57 |