Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
TonyR
Frequent Visitor

DAX help - Invoices & customers

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

1 ACCEPTED 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

http://www.azeo.com/

View solution in original post

4 REPLIES 4

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

http://www.azeo.com/

Thx @Datatouille 

Perfect Solution 🙂

Another education 🙂

Thx so much,

 

Tony

 

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.