DAX Query is slow when used in Cumulative over Date table

[SanjeevSoni]

We are developing a Data Model for Project Control. One of the Query, when we use for one day, is works but quite slow, Below is the Snapshot from DAX Studio

When We are using over Calendar; it is very slow and not usable. 

Below is the DAX Query: Each measure is referring to many Measures, 

%age Progress Actual:=DIVIDE (

CALCULATE (

SUMX (

VALUES( dMEDRDoc[Doc No] ),

[MH Actual Cutoff MEDR] * [%age Prog based on Return date] / 100

),

FILTER (

ALL ( ProjectCalendar ),

ProjectCalendar[Date] <= MAX ( ProjectCalendar[Date] )

)

),

[Total MH Based on MEDR]

)

Cheers!! Soni,  + 66 852434420

[amitchandak]

@SanjeevSoni , Check if there ate bi-direction joins. Try to 1 to Many single directional joins.

What are formula of these two

[MH Actual Cutoff MEDR] ,  [%age Prog based on Return date]

is there an opportunity to make this as a calculated column? -[MH Actual Cutoff MEDR] * [%age Prog based on Return date]

it will move part of the cost to data loading,

[SanjeevSoni]

Thanks,  I shall prepae the data files and share .

Also I am exploring if I can move the some of the complex measure to Calculated Columns or get in mode using Power Query

[Greg_Deckler]

@SanjeevSoni - 

I have some DAX Performance Tuning articles here:

• https://community.powerbi.com/t5/Community-Blog/Performance-Tuning-DAX-Part-1/ba-p/976275
• https://community.powerbi.com/t5/Community-Blog/Performance-Tuning-DAX-Part-2/ba-p/976813

These are also good articles:

• https://maqsoftware.com/expertise/powerbi/dax-best-practices
• https://www.sqlgene.com/2019/09/27/a-comprehensive-guide-to-power-bi-performance-tuning/
• Video: https://www.sqlbi.com/tv/optimizing-analyzing-dax-query-plans-sqlbits-xii/#
• https://docs.microsoft.com/en-us/power-bi/guidance/power-bi-optimization

Sorry for the link spam but it's a broad topic and the information you have provided is limited. Couple specific comments. First, I don't like VALUES. Second, is there any way you can limit the use of ALL and use ALLEXCEPT potentially or REMOVEFILTERS or KEEPFILTERS? I question the use of a SUMX within a CALCULATE statement, that seems redundant/unnecessary but hard to say. 

Can you post sample source data and expected output?

[SanjeevSoni]

Hi Greg

Thanks for taking your time to providing the Links and other suggesstions.
Earlier I was knowing only maqsoftware.com link all other refereces and infomraiton quite usefull. 

Let me review the measure and prepare the data to share with you.

Thanks once again

[Anonymous]

Define each measure as VAR (variable) then call those variables inside the actual measure .It will make your written dax fast.