Forum Discussion
How to improve Performance for this Dax
Hi All
I Have two tables, Dim_Calendar & Fact_EwContract. There is no relationship between them
I want to find an Active contract based on Dim_Calendar Date Selection
So I created this Dax, But it took a very long time to load data in table view,
I Need some alternate solution for this Dax
Hi vengadesh_p Try this:
ActiveContracts = VAR StartofThisPeriod = LASTDATE(Dim_Calendar[Month Start Date]) VAR EndofThisPeriod = ENDOFMONTH(StartofThisPeriod) RETURN CALCULATE( [# Contracts], KEEPFILTERS( Fact_EwContract[Contracts Start Month] <= StartofThisPeriod && Fact_EwContract[Contracts End Month] > EndofThisPeriod ) )Hope this helps!!
If this solved your problem, please accept it as a solution!!
Best Regards,
Shahariar HafizHi vengadesh_p
The very obvious reason causing a performance problem here is that you are filtering a table when you only need to filter a column or columns. shafiz_p has provided a good example on how to apply a filter over a column but still keep the row context. You can also refer this blog: https://www.sqlbi.com/articles/using-keepfilters-in-dax/
3 Replies
Hi vengadesh_p Try this:
ActiveContracts = VAR StartofThisPeriod = LASTDATE(Dim_Calendar[Month Start Date]) VAR EndofThisPeriod = ENDOFMONTH(StartofThisPeriod) RETURN CALCULATE( [# Contracts], KEEPFILTERS( Fact_EwContract[Contracts Start Month] <= StartofThisPeriod && Fact_EwContract[Contracts End Month] > EndofThisPeriod ) )Hope this helps!!
If this solved your problem, please accept it as a solution!!
Best Regards,
Shahariar HafizHi vengadesh_p
The very obvious reason causing a performance problem here is that you are filtering a table when you only need to filter a column or columns. shafiz_p has provided a good example on how to apply a filter over a column but still keep the row context. You can also refer this blog: https://www.sqlbi.com/articles/using-keepfilters-in-dax/Hey vengadesh_p ,
why there is no relationship between the two tables.
The fastest way for a measure is to use the relationship between the tables to apply the measure logic.
Btw using FILTER() is not that bad.
I had cases where FILTER() worked faster than filtering a specific column.
You have to figure out for your needs.
Regards
