Poor performance after changing Date table

[theDarkPrince]

Hi everyone. This is going to be a long description (but I want to be as thorough as possible), so pls bear with me.

Here is a snapshot of the dataset I'm bringing in using DirectQuery (I have changed the names of columns for obvious reasons):

And here is the snapshot of the Dates table I am using in Import mode. It is marked as a Date table. The table was created in Power BI itself using M-Query:

Notice that the Date field in this table has contiguous dates (as opposed to the prior scenario described below). It has proper datatype i.e. Date. A relationship exists between the Date column from the DirectQuery to the Date column of the Dates(Import Mode) table. Screenshot below for details:

I am trying to use all this to plot a Violin chart such as below:

However, it's taking more than 5 minutes to render the visual. Therefore, I try to capture the SQL that is being generated by visual & sent to data source using DAX Studio. Please keep in mind that a slicer is used on this page to filter date selection. This slicer uses Date field from the Dates table. The date selection for screenshot of visual is from 2020-04-01 to 2022-03-31. Below is an excerpt from the complete query I captured in DAX Studio:

SELECT 
TOP (1000001) [t1].[num],[t1].[aggr_by],SUM(
CAST([t1].[another_num] as BIGINT)
)
 AS [a0]
FROM 

|

|

|

)
 AS [t1]
WHERE 
(
([t1].[Date] IN (CAST( '20220310 00:00:00' AS datetime),CAST( '20210416 00:00:00' AS datetime),CAST( '20210527 00:00:00' AS datetime),CAST( '20210707 00:00:00' AS datetime),CAST( '20210817 00:00:00' AS datetime),CAST( '20210927 00:00:00' AS datetime),CAST( '20211107 00:00:00' AS datetime),CAST( '20211218 00:00:00' AS datetime),CAST( '20220128 00:00:00' AS datetime),CAST( '20200523 00:00:00' AS datetime),CAST( '20200703 00:00:00' AS datetime),CAST( '20200813 00:00:00' AS datetime),CAST( '20200923 00:00:00' 
AS datetime),CAST( '20201103 00:00:00' AS datetime),CAST( '20201214 00:00:00' AS datetime),CAST( '20210124 00:00:00' AS datetime),CAST( '20210306 00:00:00' AS datetime),CAST( '20200412 00:00:00' AS datetime),CAST( '20220324 00:00:00' AS datetime),CAST( '20210610 00:00:00' AS datetime),CAST( '20210721 00:00:00' AS datetime),CAST( '20210831 00:00:00' AS datetime),CAST( '20211011 00:00:00' AS datetime),CAST( '20211121 00:00:00' AS datetime),CAST( '20220101 00:00:00' AS datetime),CAST( '20220211 00:00:00' 
AS datetime),CAST( '20200717 00:00:00' AS datetime),CAST( '20200827 00:00:00' AS datetime),CAST( '20201007 00:00:00' AS datetime),CAST( '20201117 00:00:00' AS datetime),CAST( '20201228 00:00:00' AS datetime),CAST( '20210207 00:00:00' AS datetime),CAST( '20210320 00:00:00' AS datetime),CAST( '20210430 00:00:00' AS datetime),CAST( '20200426 00:00:00' AS datetime),CAST( '20200606 00:00:00' AS datetime),CAST( '20210624 00:00:00' AS datetime),CAST( '20210804 00:00:00' AS datetime),CAST( '20210914 00:00:00'  . . . . . . .

What I don't understand is why the query has separate entries for each date in current context, in the IN operator. I believe this is the part that is causing the slowdown. I understand that it's to reflect the selection in Date slicer which is acting on the visual. But why IN operator ? Why not use relational operators to select dates in certain range to reflect this selection (like in the scenario below) ? Or why not BETWEEN operator for that matter ? What I am unable to figure out is WHY this is happening & what can I do to fix this ?

As opposed to this behaviour, previously, in the Data Model, instead of the Dates table in Import mode, I had DirectQuery table named Calendar. The following screenshot shows its content:

When this Calendar DQ was in picture, the relationship was from YYYY_MM column of Calendar to YYYY_MM of the Fact table mentioned above. (One-to-Many). The date slicer was using the Date field from Calendar query which can be seen in the screenshot above. Notice that these dates are not contiguous. I just created this as CAST(YYYY_MM + '-01' as date) to have a proper date field in the DQ table (as we don't have one in the source SQL Server table).

And even in this case of previous version of Data Model, the date selection in the slicer was same i.e. from '2020-04-01' to '2022-03-31'. But in that case, the visual would get rendered in mere seconds & the SQL query generated by the visual in the background did not make use of IN operator rather it used the follwing SQL construct to filter out the records for the context:

WHERE 
(
([t8].[DATE] < CAST( '20220302 00:00:00' AS datetime))
 AND 
([t8].[DATE] >= CAST( '20200401 00:00:00' AS datetime))
)

Here, it's using relational operators to make selection between dates as opposed to mentioning individual dates within IN clause (like above). I have tried making several changes across the report in different panes, formats etc. But still unable to improve performance while using the new Dates table (the one in Import Mode). Any help is appreciated guys. Do let me know if there's any other information required to solve this. Thanks in advance.

P.S. : Found this relevant post on the forum later after posting my own question. It's got fewer details mentioned but is similar. [Link] @TeeGee @srinivt @jcameon @v-ljerr-msft 

[v-kkf-msft · Accepted solution]

Hi @theDarkPrince ,

Composite models have some security implications. Information that's stored in the import table (Dates table) is now included in a query that's sent to the SQL Server database.

If you create a relationship between the Dates table and the DQ table, I think Power BI will store the filtered date  in a spreadsheet, and the DQ table will query the data source based on the contents of the spreadsheet. That leads to the use of IN operator in SQL Server.

Use composite models in Power BI Desktop -- microsoft 

To avoid this case, try to delete the relationship between the Dates table and the DQ table, and use the following similar measures.

Measure = 
CALCULATE(
    SUM(FactInternetSales[Freight]),
    FILTER(
        FactInternetSales,
        FactInternetSales[OrderDateKey] >= MIN(DimDate[DateKey])
        && FactInternetSales[OrderDateKey] <= MAX(DimDate[DateKey])
    )
)

This is the result of query tracking in two ways:

1. Create relationship between tables:

     2. Create measures:

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.