Super slow formula when evaluating against date table

[Clolawra]

Hi all,

I am new to the forum and looking forward to start working my way through the learning content, however in the meantime I am stuck on a DAX query which I am trying to fix which was written by a previous colleague. There are huge amounts of sensitive data in the pbix file as it is our banking data, so I was hoping someone may be able to take a look at the query and let me know if anything jumps out immediately as being bad practice.

Measure = VAR theLastDate=MAX(Dates[Date])
RETURN CALCULATE(
        SUMX(
            FILTER( 'All Balances',
                    'All Balances'[AsAtDate] =  CALCULATE(
                                                            MAX('All Balances'[AsAtDate]),
                                                            FILTER( 'All Balances',
                                                                    'All Balances'[AccountNo]=EARLIER('All Balances'[AccountNo])
                                                            ),
                                                            'All Balances'[AsAtDate]<=theLastDate,
                                                            FILTER(ALL(Dates),[Is Not Future])  --Exclude future values
                                                        ) &&
                    'All Balances'[AsAtDate]<=theLastDate
            ),
            'All Balances'[Balance_ABS]
        ),
        'All Balances'[AsAtDate]<=theLastDate,
        FILTER(ALL(Dates),[Is Not Future])  --Exclude future values
)

It seems to be very repetitive and I can’t quite figure out whether the EARLIER function is being used correctly.

The measure alone is okay, however when evaluated against the date table it is slow.

So far I have done the below (highlighted in bold) to try and improve the speed:
Measure = VAR theLastDate=MAX(Dates[Date])
RETURN CALCULATE(
SUMX(
FILTER( ‘All Balances’,
‘All Balances’[AsAtDate] = CALCULATE(
MAX(‘All Balances’[AsAtDate]),
FILTER( ‘All Balances’,
‘All Balances’[AccountNo]=EARLIER(‘All Balances’[AccountNo])
),
‘All Balances’[AsAtDate]<=theLastDate,
FILTER(ALL(Dates**[Date]**), Dates[Date] <= Today() --Exclude future values
), USERELATIONSHIP(‘All Balances’[AsAtDate], Dates[Date]) &&
‘All Balances’[AsAtDate]<=theLastDate
),
‘All Balances’[Balance_ABS]
),
‘All Balances’[AsAtDate]<=theLastDate,
FILTER(ALL(Dates),[Is Not Future]) --Exclude future values
)

There is already a relationship between the AsAtDate column and the Dates date column.

Another thing I tried was using TREATAS(VALUES(Dates’[Date]), AllBalances[AsAtDate]) instead of the USERELATIONSHIP formula

This worked really quickly in DAX Studio, however in Power BI itself, it said my formula was too complex and I got a memory failure too.

Hopefully I have provided enough information, I always find it tricky picking up someone else’s work as it’s hard to understand if there was a reason why they have written it that way. I feel like I could be missing something really obvious hence posting here
Thanks in advance!!Preformatted text

[EnterpriseDNA]

Hi @Clolawra, I noticed you didn’t provide a PBIX file. Providing one will help users and experts find a solution to your inquiry faster and better.

A perfect initial question includes all of the following:

• A mockup of the results you want to achieve
• Your current work-in-progress PBIX file
• Your underlying data file (to allow us to go into Power Query if necessary to transform your data and/or data model – often DAX questions really end up being data modeling solutions)

Check out this thread on Tools and Techniques for Providing PBIX Files with Your Forum Questions

Not completing your data may sometimes cause delay in getting an answer.

[EnterpriseDNA]

Hi @Clolawra, we’ve noticed that no response has been received from you since July 5th.

We are waiting for the masked demo pbix file and any other supporting links and details.

In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.

[Clolawra]

Hi, this is a huge report and I am not able to mask the data and post it because it is highly confidential financial data, would it be possible for the DAX formula above to be reviewed to confirm whether it is following best practice please?

[ankit]

Hi @Clolawra - We ideally need the sample PBIX file to help to check how the Relationships are setup and what changes we suggest are actually giving correct results.

Hi @AntrikshSharma, @BrianJ - Can you provide suggestions without PBIX.

Thanks
Ankit J

[EnterpriseDNA]

@ankit

need the sample PBIX file to help to check how the Relationships are setup and what changes we suggest are actually giving correct results.

Hi @Clolawra. Here are some videos that may help you masking sensitive data and create datasets and data models representative of your problem:

How To Mask Sensitive Data In Power BI

Free Power BI Resource Collection Including Dataset Builder Tool

[BrianJ]

@Clolawra ,

I can tell by looking at it that there is a lot of inefficiency in this measure, but I’m not able to rework it in my head without some sample data to play with and break the measure down into its components and rebuild it more efficiently. Perhaps our resident DAX Mozart, @AntrikshSharma can do that, but I will definitely need just some representative data to make progress on this. Doesn’t have to be a lot of data, nor the full data model - just representative data of the same types and organized into the same tables with the same relationships as the relevant tables from the full model.

The EDNA Data Randomizer tool (discussed in the second video able) may be just the ticket for developing this representative dataset quickly.

• Brian

[EnterpriseDNA]

Hi @Clolawra, we’ve noticed that no response has been received from you since July 8th.

We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.

[Clolawra]

Hi, please leave this with me as I am going to work on anonymising the data over the next day or so and provide a copy for you

[BrianJ]

@Clolawra ,

A suggestion - when you submit the anonymized data, submit it as a new post under the Optimization Lab category.

Optimization Lab is a relatively new initative we started this Spring to do a deep dive focus on submitted measures that are running slowly. It is led by our resident DAX superstar @AntrikshSharma , and for the selected measures he does a deep dive video where he walks through and explains why the measure is running slowly and what can/should be done to optimize it. I think your measure would be an excellent candidate for the Lab.

Introducing A New Enterprise DNA Initiative – Optimization Lab

All, As an adjunct to the excellent new Optimizing DAX course that we premiered in January, we are starting a new initiative this month called Optimization Lab, featuring Enterprise DNA expert @AntrikshSharma. If you are a regular on this forum, you undoubtedly have come across Antriksh’s amazing DAX deep-dive explanations of key DAX concepts, like this one and this one. In addition to his virtuoso DAX coding skills, he is also incredibly knowledgeable about the intricacies of DAX optimization. …

• Brian

[EnterpriseDNA]

Hi @Clolawra, did the response provided by @BrianJ help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query. Thanks!

[EnterpriseDNA]

Hi @Clolawra, we’ve noticed that no response has been received from you since July 5th. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.

[EnterpriseDNA]

Hi @Clolawra, due to inactivity, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box.