Forum Discussion
Direct Query - Time Function - Slow Performance
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 ResultThank you for help.
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.
7 Replies
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/434008DirectQuery 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
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.
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 KumarHi 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
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 KumarHello,
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.
