Direct Query - Time Function - Slow Performance

[Pawel_1990]

Hello, 

I am using Direct Query Snowflake table as data source. One of measures is working slow. I was thinking if DAX can be written in other way to speed up performance. 

Dashboards has main date table and secondary date table. Slicer on each page is added and forced single select to pick only 1 month. On some pages visual showing last 13 months is added as well.

Dax code below:

Measure #1 - Sum loggin 

#Scale = 
VAR SCALE = CALCULATE(SUM(Table1[LOGIN_COUNT]),
Table1[AGGREGATION_TYPE] = "Type1", 
KEEPFILTERS(Dim_Country[COUNTRY_CODE] <> BLANK()))

RETURN
SCALE

Measure #2 - To show last 13 months on visual, when only 1 month selected on slicer

#Scal 13M = 
VAR NumOfMonths = -13
VAR Min _Date_13M = EOMONTH(MAX ( 'MAIN_DATE'[CALENDAR_DATE]), -13)
VAR ReferenceDate = MAX ( 'MAIN_DATE'[CALENDAR_DATE])
VAR PreviousDates =
    FILTER(DATESINPERIOD (
        'SECONDARY_DATE'[CALENDAR_DATE],
        ReferenceDate,
        NumOfMonths,
        MONTH
    ), 'SECONDARY_DATE'[CALENDAR_DATE] > Min _Date_13M)
VAR Result =
    CALCULATE ([#Scale],
        REMOVEFILTERS(MAIN_DATE),
        KEEPFILTERS ( PreviousDates ),
        USERELATIONSHIP('SECONDARY_DATE'[CALENDAR_DATE], MAIN_DATE[CALENDAR_DATE])
    )
RETURN Result

Thank you for help. 

[v-pgoloju]

Hi @Pawel_1990,

Just following up to see if the Response provided by community members were helpful in addressing the issue.

If one of the responses helped resolve your query, please consider marking it as the Accepted Solution. Feel free to reach out if you need any further clarification or assistance.

Best regards,
Prasanna Kumar

[v-pgoloju]

Hi @Pawel_1990,

Just following up to see if the Response provided was helpful in resolving your issue. Please feel free to let us know if you need any further assistance.

Best regards,

Prasanna Kumar

[v-pgoloju]

Hi @Pawel_1990,

Thank you for reaching out to the Microsoft Fabric Forum Community, and special thanks to @Aburar_123 , @FBergamaschi and @Greg_Deckler  for prompt and helpful responses.

Just following up to see if the Response provided by community members were helpful in addressing the issue.

If one of the responses helped resolve your query, please consider marking it as the Accepted Solution. Feel free to reach out if you need any further clarification or assistance.

Best regards,
Prasanna Kumar

[Aburar_123]

Hi @Pawel_1990 ,

Please try this visual filter logic.

My Data Model:

IS_IN_LAST13_MONTHS = IF(MAX(DIM_DATE[Date])>EOMONTH(MAX(DIM_DATE1[Date]),-13) && MAX(DIM_DATE[Date])<=MAX(DIM_DATE1[Date]),1,0)

Thank you.

[FBergamaschi]

DirectQuery is always slower than Import

Your DAX is OK but performance in directquery depends on the source structure, internet connection and so forth

Why not using Import instead of DirectQuery?

If this helped, please consider giving kudos and mark as a solution

@me in replies or I'll lose your thread

Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page

Consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI

[Greg_Deckler]

@Pawel_1990 If the time function is causing you issues, you could try an alternative. You may find this helpful - https://community.powerbi.com/t5/Community-Blog/To-bleep-With-Time-Intelligence/ba-p/1260000

Also, see if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...