How to improve Performance for this Dax

[vengadesh_p]

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

[shafiz_p · Accepted solution]

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 Hafiz

[sergej_og]

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

[danextian · Accepted solution]

Hi @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/ 

[shafiz_p · Accepted solution]

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 Hafiz