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
nmaddock
Helper I
Helper I

IF statement crashes report

Hi,

 

I need to change one of my dax calculation below to check if the Agreement end date has a value or not because if it does this end date needs to be used. However the IF statement crashes the report.

 

 

Charge Lifts over last 13 weeks = 
CALCULATE( 
     SUM(Job[Charged Quantity]), 
     FILTER(Job, Job[Date] >= [DB Backup Date] -90),
     FILTER(Job, Job[Date] <= [DB Backup Date] -0)         
)

 

Event this Dax would crash the report:

IF(MAX(Agreement[Agreement End Date]) = BLANK(), 10, 20)
     

This is what I really need to change the dax formula too:

IF(MAX(Agreement[Agreement End Date]) = BLANK(),
// If no agreement end date use the db backup date
  CALCULATE(SUM(Job[Charged Quantity]), 
   
    FILTER(Job, Job[Date] >= [DB Backup Date] -90),
    FILTER(Job, Job[Date] <= [DB Backup Date] -0)   
      
 ), 
// If the agreement end date is after the db backup date use the db backup date
     IF(MAX(Agreement[Agreement End Date]) > [DB Backup Date], 
     CALCULATE(SUM(Job[Charged Quantity]), 
   
    FILTER(Job, Job[Date] >= [DB Backup Date] -90),
    FILTER(Job, Job[Date] <= [DB Backup Date] -0)   
      
 ), 
// Has agreement end date and the agreement endate is before the db backup date
 CALCULATE(SUM(Job[Charged Quantity]), 
   
    FILTER(Job, Job[Date] >= MAX(Agreement[Agreement End Date]) -90),
    FILTER(Job, Job[Date] <= MAX(Agreement[Agreement End Date]) -0)  

 ))))

pbix link is below, any help is appreciated as I'm totally stuck in how to resolve this issue.

 

https://www.dropbox.com/s/l0pj9rbvrwtus7k/Termination%20complete%20-%20Copy.pbix?dl=0

 

The report on Terminations and Price Increase crashes.

 

Thanks in advanced Nathan

1 REPLY 1
v-chuncz-msft
Community Support
Community Support

@nmaddock 

 

I cannot reproduce the issue with the latest version. You may take a look at https://www.sqlbi.com/topics/optimization/.

 

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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.