i need to optimize my dax query .. any help is app...

spradhan_pm · ‎03-16-2024

i have
a distinct user table
5 diff (many side relationship table to user ) - Jobs table, dealer table, hire table, termination table.. etc

i have about 55000+ distinct users and a total of 2.6M rows of data in total

User_shadow table: ( externalcode is used as id )

ExternalCode	name
123	james
124	joe
125	jill
126	jay

job_shadow table:

ExternalCode	primary_job_cd	date
123	1	01/02/2020
123	2	02/01/2020
124	2	01/01/2020
124	3	03/01/2020

Dealer_shadow table:

ExternalCode	dlr_cd	date
123	112	01/02/2020
123	211	03/01/2020
124	122	01/01/2020
124	112	04/01/2020

calculation max in job (same logic to get each max dealer, hiredate, terminated date as well):

latestJob_max = 

    var SelectedMaxDate = 
    MAX('Calendar'[Date])
    //[relative_date_max]

VAR MaxDateBefore = 
    CALCULATE(
        MAX(Job_Shadow[umd_date]), 
        ALL(Job_Shadow[umd_date],Job_Shadow[PRIMARY_JOB_CD]), // This removes filters only from umd_date and ExternalCode
        // Job_Shadow[ExternalCode] = "D00031301",
        Job_Shadow[umd_date] < SelectedMaxDate,
        REMOVEFILTERS(Job_Lookup)
    )

VAR Result = 
    CALCULATE(
        MAX(Job_Shadow[PRIMARY_JOB_CD]),
        Job_Shadow[PRIMARY_JOB_CD] in VALUES(Job_Lookup[JobID]),
        Job_Shadow[umd_date] = MaxDateBefore
    )

RETURN
    Result

ending count dax:

count_max = 


    var SelectedMaxDate = 
	 Max('Calendar'[Date])
        //[relative_date_max]
    var SelectedMinDate = 
        //[relative_date_min]
	Min('Calendar'[Date])
		//  DATE(2024,2,31)

		
	var selectedDealer = 
        Values('Dealer/District/Zone/Region_Lookup'[DealerID])
//    SELECTEDVALUE('Dealer/District/Zone/Region_Lookup'[DealerID])
    //  "10130"

    
	var selectedJob = 
        Values(Job_Lookup[JobID])
//    SELECTEDVALUE(Job_Lookup[JobID])
		// "D102"
		

        
		
	var selectedTenure = 
        Values(TenureSort_Reference[TenureSort])

    
	var selectedDriveR = 
        Values(DriveParticipation_Lookup[Column1])
        
return
Calculate(
    Count(User_Shadow[ExternalCode]),
    FILTER(
        User_Shadow,
        Calculation_shadow[latestDealer_max] in selectedDealer
//        && Calculation_shadow[latestJob_max] in selectedJob
//        && Calculation_shadow[latestTenure_max] in selectedTenure
//        && Calculation_shadow[latestDriveR_max] in selectedDriveR
        && NOT(Calculation_shadow[latestJob_max]  IN {"D345", "D346", "D347", "D365", "D366", "D367"})
        && LEFT(Calculation_shadow[latestJob_max], 1) = "D"
        
        &&
         Calculation_shadow[latestHire_max] <= SelectedMaxDate
        && (
            ISBLANK(Calculation_shadow[latestFire_max])
            || ( 
                not(DATEVALUE(Calculation_shadow[latestFire_max]) > DATEVALUE(Calculation_shadow[latestHire_max]))
                )
        )
    )
        
    ,CROSSFILTER(
        'Job_Lookup'[JobID],
        Job_Shadow[PRIMARY_JOB_CD]
        ,None
    )
    ,CROSSFILTER(
        'Dealer/District/Zone/Region_Lookup'[DealerID],
        Dealer_Shadow[DLR_CD]
        ,None
    )
)+0

startin count dax:

count_min = 


    var SelectedMaxDate = 
	Min('Calendar'[Date])
        //[relative_date_min]
		//  DATE(2024,2,31)

		
	var selectedDealer = 
        Values('Dealer/District/Zone/Region_Lookup'[DealerID])
//    SELECTEDVALUE('Dealer/District/Zone/Region_Lookup'[DealerID])
    //  "10130"

    
	var selectedJob = 
        Values(Job_Lookup[JobID])
//    SELECTEDVALUE(Job_Lookup[JobID])
		// "D102"
		
	var selectedTenure = 
        Values(TenureSort_Reference[TenureSort])

    
	var selectedDriveR = 
        Values(DriveReimbursement_Shadow[DriveParticipation])

RETURN
CALCULATE (
    COUNT(User_Shadow[ExternalCode]),
    FILTER(
        User_Shadow,
        Calculation_shadow[latestDealer_min] in selectedDealer
        && Calculation_shadow[latestJob_min] in selectedJob
        && Calculation_shadow[latestTenure_min] in selectedTenure
        && Calculation_shadow[latestDriveR_min] in selectedDriveR
        
        &&
            NOT(Calculation_shadow[latestJob_min]  IN {"D345", "D346", "D347", "D365", "D366", "D367"})
        && LEFT(Calculation_shadow[latestJob_min], 1) = "D"
        
        


        &&
        Calculation_shadow[latestHire_min]  <= SelectedMaxDate
        && (
            ISBLANK(Calculation_shadow[latestFire_min])
            || ( 
                    Not(DATEVALUE(Calculation_shadow[latestFire_min]) > DATEVALUE(Calculation_shadow[latestHire_min] ))

                )
                
        )
    )
    
    ,CROSSFILTER(
        'Job_Lookup'[JobID],
        Job_Shadow[PRIMARY_JOB_CD]
        ,None
    )
    ,CROSSFILTER(
        'Dealer/District/Zone/Region_Lookup'[DealerID],
        Dealer_Shadow[DLR_CD]
        ,None
    )
)+0

average count dax:

count_avgEmp = DIVIDE([count_min] + [count_max],2,0)

terminated count dax:

count_terminated_range = 


    var SelectedMaxDate = 
	Max('Calendar'[Date])
        //[relative_date_max]
    var SelectedMinDate = 
        //[relative_date_min]
	Min('Calendar'[Date])
		//  DATE(2024,2,31)

		
	var selectedDealer = 
        Values('Dealer/District/Zone/Region_Lookup'[DealerID])
//    SELECTEDVALUE('Dealer/District/Zone/Region_Lookup'[DealerID])
    //  "10130"

    
	var selectedJob = 
        Values(Job_Lookup[JobID])
//    SELECTEDVALUE(Job_Lookup[JobID])
		// "D102"
		

        
		
	var selectedTenure = 
        Values(TenureSort_Reference[TenureSort])

    
	var selectedDriveR = 
        Values(DriveParticipation_Lookup[Column1])
        
return
CALCULATE (
    COUNT(User_Shadow[ExternalCode]),
    FILTER(
        User_Shadow,
        

            [latestJob_max] in selectedJob
        && [latestTenure_max] in selectedTenure
        && [latestDriveR_max] in selectedDriveR
        
        && NOT(Calculation_shadow[latestJob_max]  IN {"D345", "D346", "D347", "D365", "D366", "D367"})
        && LEFT(Calculation_shadow[latestJob_max], 1) = "D"
        
        &&

        
        (
            (
                Not(ISBLANK([latestFire_max]))
                &&  (

                    [latestDealer_max] in selectedDealer 
                    && SelectedMinDate <= Calculation_shadow[latestFire_max] 
                    &&  Calculation_shadow[latestFire_max] < SelectedMaxDate
                    && 
                        


                        format(Calculation_shadow[latestFire_max] , "YYYYMMDD") > format(Calculation_shadow[latestHire_max] , "YYYYMMDD")

                )
                
                
                
            )
                || 
            (
                    SelectedValue('Dealer/District/Zone/Region_Lookup'[DealerID])
                && [latestDealer_min] in selectedDealer
                && Not( [latestDealer_max] = [latestDealer_min] )
                
            )
        )

        
        
        // 'Hire_Shadow'[TRMNTN_DT] >= SelectedMinDate
        // && 'Hire_Shadow'[TRMNTN_DT] <= SelectedMaxDate
    )
    
    ,CROSSFILTER(
        'Job_Lookup'[JobID],
        Job_Shadow[PRIMARY_JOB_CD]
        ,None
    )
    ,CROSSFILTER(
        'Dealer/District/Zone/Region_Lookup'[DealerID],
        Dealer_Shadow[DLR_CD]
        ,None
    )
)+0

turnover percentage dax:

turnover_master = 
    DIVIDE([count_terminated_range], [count_avgEmp], 0)

it takes atleast a minute to generate a monthly line chart for turnover for a single years timeline.
how do i reduce the load time and optimize this query?

any help is appreciated. thanks

Sahir_Maharaj · ‎03-17-2024

Hello @spradhan_pm,

Can you please try the following:

1. Optimize latestJob_max Calculation

latestJob_max = 
VAR SelectedMaxDate = MAX('Calendar'[Date])
VAR MaxDateBefore = CALCULATE(MAX(Job_Shadow[umd_date]), Job_Shadow[umd_date] < SelectedMaxDate)
RETURN CALCULATE(MAX(Job_Shadow[PRIMARY_JOB_CD]), Job_Shadow[umd_date] = MaxDateBefore)

2. Simplify the count_max and count_min Calculations

count_max = 
VAR SelectedMaxDate = MAX('Calendar'[Date])
VAR SelectedMinDate = MIN('Calendar'[Date])
VAR ValidJobIDs = EXCEPT(VALUES(Job_Lookup[JobID]), {"D345", "D346", "D347", "D365", "D366", "D367"})
RETURN
CALCULATE(
    COUNTROWS(User_Shadow),
    FILTER(
        User_Shadow,
        User_Shadow[latestJob_max] IN ValidJobIDs &&
        LEFT(User_Shadow[latestJob_max], 1) = "D" &&
        User_Shadow[latestHire_max] <= SelectedMaxDate &&
        (ISBLANK(User_Shadow[latestFire_max]) || User_Shadow[latestFire_max] > User_Shadow[latestHire_max])
    )
)

3. Revise count_terminated_range for Better Performance

count_terminated_range = 
VAR SelectedMaxDate = MAX('Calendar'[Date])
VAR SelectedMinDate = MIN('Calendar'[Date])
VAR ValidJobIDs = EXCEPT(VALUES(Job_Lookup[JobID]), {"D345", "D346", "D347", "D365", "D366", "D367"})
RETURN
CALCULATE(
    COUNTROWS(User_Shadow),
    FILTER(
        User_Shadow,
        User_Shadow[latestJob_max] IN ValidJobIDs &&
        LEFT(User_Shadow[latestJob_max], 1) = "D" &&
        NOT(ISBLANK(User_Shadow[latestFire_max])) &&
        User_Shadow[latestFire_max] >= SelectedMinDate &&
        User_Shadow[latestFire_max] < SelectedMaxDate &&
        User_Shadow[latestFire_max] > User_Shadow[latestHire_max]
    )
)

4. Reduce turnover_master Complexity

turnover_master = DIVIDE([count_terminated_range], [count_avgEmp], 0)

Hope this helps!

Did I answer your question? Mark my post as a solution, this will help others!

If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

Kind Regards,
Sahir Maharaj
Data Scientist | Data Engineer | Data Analyst | AI Engineer
P.S. Want me to build your Power BI solution? (Yes, its FREE!)