topic Re: Direct Query - Time Function - Slow Performance in DAX Commands and Tips

Direct Query - Time Function - Slow Performance

Pawel_1990 — Thu, 14 Aug 2025 15:37:08 GMT

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.

Re: Direct Query - Time Function - Slow Performance

Greg_Deckler — Thu, 14 Aug 2025 16:09:05 GMT

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-TITHW/m-p/434008

Re: Direct Query - Time Function - Slow Performance

FBergamaschi — Tue, 19 Aug 2025 16:51:12 GMT

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

Re: Direct Query - Time Function - Slow Performance

Aburar_123 — Wed, 20 Aug 2025 02:46:39 GMT

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.

Re: Direct Query - Time Function - Slow Performance

Anonymous — Wed, 20 Aug 2025 06:08:35 GMT

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

Re: Direct Query - Time Function - Slow Performance

Anonymous — Fri, 22 Aug 2025 03:25:03 GMT

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

Re: Direct Query - Time Function - Slow Performance

Anonymous — Mon, 25 Aug 2025 09:56:35 GMT

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

Re: Direct Query - Time Function - Slow Performance

Pawel_1990 — Fri, 29 Aug 2025 10:36:56 GMT

Hello,
Topic may be closed, biggest issue was with masking at snowflake level - my other topic:
https://community.fabric.microsoft.com/t5/Desktop/SnowFlake-Direct-Query-Slow-Perfomrance-Adding-Role-at-Power/m-p/4811141#M1430972
When solving this issue, metric started to work correctly again. Thanks all for help, learned a lot.