Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Speed this up! (please!)

I have a basic data model with 4 tables: 1 fact table and 3 dimensions table. One of the dimension tables is a date table. The fact table contains c. 1.1 million rows.   This is the measure I would...
Need Help
Tips and Tricks
amitchandak's avatar
amitchandak
Super User
2 years ago

Anonymous , Try like

 

VAR WeekEnding = SELECTEDVALUE(Dates[FinancialWeekEnding],MAX(Dates[FinancialWeekEnding]))


RETURN
CALCULATE(COUNTROWS(Fact),
filter(Fact,
Fact[Completed Date Calculated] > WeekEnding || ISBLANK(Fact[Completed Date Calculated]),
Fact[IssuedDate] <= WeekEnding,
Fact[Cancelled Date Calculated] > WeekEnding || ISBLANK(Fact[Cancelled Date Calculated])
), all(Date) )


or use crossfilter to remove the join

example

Power BI: HR Analytics - Employees as on Date : https://youtu.be/e6Y-l_JtCq4
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970
Power BI HR Active Employee Tenure Bucketing, and Hired, Terminated, and Active employees: https://youtu.be/fvgcx8QLqZU

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi, I am not sure that is going to work as FILTER accepts 2 arguments only?

     

    I can remove the commas and replace with && but that just returns a blank value. I'm not sure why using FILTER would improve the calcualtion speed?