Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

DAX optimisation row-wise calculation

Hello guys, need some help.

 

I have to calculate listed employee number - its average employee number.

I have calculated two measures:

Employee count = 
VAR total = CALCULATE(
        DISTINCTCOUNT(Employee[No_]);
        FILTER(ALL('Calendar');
       'Calendar'[Date]<=MAX('Calendar'[Date]));USERELATIONSHIP('Calendar'[Date];Employee[Data_Joined]))
VAR fired =    CALCULATE(
        COUNTA(Employee[Data_Left]);
        FILTER(ALL('Calendar');
       'Calendar'[Date]<=MAX('Calendar'[Date]));USERELATIONSHIP(Employee[Data_Joined];'Calendar'[Date]))
RETURN
IFERROR(total-fired;BLANK())
Listed employee = AVERAGEX('Calendar';Employee[Employee count])
The calculation is ok, but it took quite a long time with uploading to the table.
With new Performance Analyze it shows that it took 79096 ms for the table to upload
 
Please, can anyone help me to improve performance?
Pbix file (I can't make active relationships because of additional tables that I use in production)
 
 
DAX
employee
optimize

2 Replies

  • parry2k's avatar
    parry2k
    Super User
    7 years ago

    Anonymous try this:

     

    Listed employee = AVERAGEX(VALUES('Calendar'[Year]),Employee[Employee count])
    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Thanks, parry2k , for support, but I need this measure to be dynamic.

      It should calculate for Year, Month or Day.