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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Calculated column not returning expected results

Hi Community

 

I have a PBI report with 2 main tables. Service Cases and Invoice Details. 

On the Service Case table I have a column that identifies for each record the date of that customers next order AFTER the date the case was raised. The DAX i am using is as follows;

Next Order = IF('Case List'[Re Ordered],CALCULATE(min('Invoice List'[Invoice Date]),FILTER('Invoice List','Invoice List'[Customer]='Case List'[Customer] && 'Invoice List'[Invoice Date]> 'Case List'[Created On])),0)

[Re Ordered] is a boolean field that indicates if the customer for that record has subsequently re ordered. It uses similar DAX;

COUNTX(FILTER('Invoice List','Invoice List'[Customer]='Case List'[Customer] && 'Invoice List'[Invoice Date]> 'Case List'[Created On]),'Invoice List'[Name]) <> 0

I have used min('Invoice List'[Invoice Date]) so i get the first [Invoice Date] in Invoice List for that [Customer] after the [Created On] date. 

'Case List'[Customer] and 'Invoice List'[Customer] are hexdecimal ID strings

'Invoice List'[Invoice Date] and 'Case List'[Created On] are both formated as dd/mm/yy hh:mm:ss.

Mostly this is working correctly but there are several records where it is not returning any value.

If i apply the same filtering logic manually to 'Invoice List' for each of the records retunring no value, i have a valid record to return the date on.

What am i missing / doing wrong?

3 REPLIES 3
Anonymous
Not applicable

Sorry, just to clarify Service Cases and Invoice Details tables are named 'Invoice List' and 'Case List'

Anonymous
Not applicable

Can you post the pbix file?  Onedrive/dropbox works. It's hard, at least for me, to go through your formula without any formatting. thx

Anonymous
Not applicable

Hi Nick,

Thanks for taking the time to respond.

 

I fear that is not possible. There is a lot of sensitive data in there not to mention the connection data to our Dynamics 365 CRM.

Copying the tables and stripping out the sensitive data and creating a copy pbix would be a long job.

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.