Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

How drill through works with direct query

Hello All,

 

Can any one please quide or clear my confusion about how drill through works with direct query?

 

I have create a home page with table which contains country sales and the data source to this perticular table visual is IMPORT.

 

Now i have another report page, in that i have a table visual but at country and states level but the data source is got by using Direct Query.

 

Now when ever i drill through from page 1 to page 2, 

 

Q1 :- Does it quries all the records ?

Q2:- Or does it pass that drillthrough field into the TSQL query as a WHERE clause?

Q3:- Or it jus loads the data what we have in powerbi?

 

Please clear this.

 

Thanks,

Mohan V.

1 ACCEPTED SOLUTION

@Anonymous It's a great question and as per my finding, on drill thru page, on the direct query, query fold will happen, means where clause will happen at the back end, and only required data will come to the power bi.

 

Here is how I tested, Azure SQL Server, used direct query, on drill thru page, create a table visual from this direct query table, and added campaign and name as drill thru which are part of two another tables, agent and campaign (entered data directly in power bi, it can be import as well). One to many relationships between these two tables with direct query SQL table.

 

Created a page, with two table visuals, added agent, and campaign in each table visual. on right-click on one of the rows in the table visual, drill thru to the page where SQL server table using a direct query is visualized. 

 

These are the queries passed to the Azure SQL when drill thru happens

 

//DRIL THRU USING AGENT FROM MAIN PAGE

// DAX Query
DEFINE VAR __DS0FilterTable = 
  TREATAS({"Computers"}, 'Campaign'[Campaign])

EVALUATE
  TOPN(
    502,
    SUMMARIZECOLUMNS(
      ROLLUPADDISSUBTOTAL(
        ROLLUPGROUP('DimCall'[AgentName], 'DimCall'[Campaign], 'DimCall'[Company]), "IsGrandTotalRowTotal"
      ),
      __DS0FilterTable,
      "CountAgentName", CALCULATE(COUNTA('DimCall'[AgentName]))
    ),
    [IsGrandTotalRowTotal],
    0,
    'DimCall'[AgentName],
    1,
    'DimCall'[Campaign],
    1,
    'DimCall'[Company],
    1
  )

ORDER BY
  [IsGrandTotalRowTotal] DESC,
  'DimCall'[AgentName],
  'DimCall'[Campaign],
  'DimCall'[Company]


// SQL Query

SELECT 
TOP (1000001) [t0].[AgentName],[t0].[Company],
COUNT_BIG([t0].[AgentName])
 AS [a0]
FROM 
(
(select [$Table].[CallId] as [CallId],
    [$Table].[CallDate] as [CallDate],
    [$Table].[AgentName] as [AgentName],
    [$Table].[Company] as [Company],
     [$Table].[CRMCaseID] as [CRMCaseID]
from [dbo].[DimCall] as [$Table])
)
 AS [t0]
WHERE 
(
[t0].[Campaign] = N'Computers'
)

GROUP BY [t0].[AgentName],[t0].[Company] 


//DRIL THRU USING CAMPAIGN FROM MAIN PAGE

// DAX Query
DEFINE VAR __DS0FilterTable = 
  TREATAS({"Cedric Daughtery"}, 'Agent'[Agent])

EVALUATE
  TOPN(
    502,
    SUMMARIZECOLUMNS(
      ROLLUPADDISSUBTOTAL(
        ROLLUPGROUP('DimCall'[AgentName], 'DimCall'[Campaign], 'DimCall'[Company]), "IsGrandTotalRowTotal"
      ),
      __DS0FilterTable,
      "CountAgentName", CALCULATE(COUNTA('DimCall'[AgentName]))
    ),
    [IsGrandTotalRowTotal],
    0,
    'DimCall'[AgentName],
    1,
    'DimCall'[Campaign],
    1,
    'DimCall'[Company],
    1
  )

ORDER BY
  [IsGrandTotalRowTotal] DESC,
  'DimCall'[AgentName],
  'DimCall'[Campaign],
  'DimCall'[Company]


// SQL Query

SELECT 
TOP (1000001) [t0].[Campaign],[t0].[Company],
COUNT_BIG([t0].[AgentName])
 AS [a0]
FROM 
(
(select [$Table].[CallId] as [CallId],
     [$Table].[CallDate] as [CallDate],
    [$Table].[AgentName] as [AgentName],
    [$Table].[Company] as [Company],
     [$Table].[CRMCaseID] as [CRMCaseID]
from [dbo].[DimCall] as [$Table])
)
 AS [t0]
WHERE 
(
[t0].[AgentName] = N'Cedric Daughtery'
)

GROUP BY [t0].[Campaign],[t0].[Company] 

 

clear filter and grouping are sent back to SQL server and only the required result is returned to the report. I hope this answered your question and provided the clarification you need.

 

I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.

 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

View solution in original post

4 REPLIES 4
Mariusz
Community Champion
Community Champion

Hi @Anonymous 

 

If you are using a mixed model and you want to to get the best performance the table that is set as an import and is available in the direct query data source should be set as both so the engine can switch between an import and direct query.

 

If the import table is not available in the DQ data source, then the engine will pass the key(s) column values when the folding takes place, but this is related to your privacy setting as well.

 

The best way to investigate what queries are sent back is by using DAX studio.

 

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

 

amitchandak
Super User
Super User

@Anonymous ,

I do not see any details showing that it will not work on Direct Query, So it should work.

Power Bi generate query based on visual and filters, so should not query complete data

 

You can sql profile to check queries

https://radacad.com/directquery-connection-in-power-bi-how-does-it-work-limitations-and-advantages

Anonymous
Not applicable

Dear @amitchandak ,

 

Yes i do know that drillthrough works with Direct query.

But my question is how it works?

 

I see that there is same question i found in community but not satisfied answer.

https://community.powerbi.com/t5/Service/Drillthrough-to-another-report-direct-query-does-it-filter-...

 

Q1:- Does Drill though filter values, are works as a where clause condition in directquery?

Q2: or instead of where clause is it jus load all the records??

 

Please clarify.

Thanks,

Mohan V

 

 

@Anonymous It's a great question and as per my finding, on drill thru page, on the direct query, query fold will happen, means where clause will happen at the back end, and only required data will come to the power bi.

 

Here is how I tested, Azure SQL Server, used direct query, on drill thru page, create a table visual from this direct query table, and added campaign and name as drill thru which are part of two another tables, agent and campaign (entered data directly in power bi, it can be import as well). One to many relationships between these two tables with direct query SQL table.

 

Created a page, with two table visuals, added agent, and campaign in each table visual. on right-click on one of the rows in the table visual, drill thru to the page where SQL server table using a direct query is visualized. 

 

These are the queries passed to the Azure SQL when drill thru happens

 

//DRIL THRU USING AGENT FROM MAIN PAGE

// DAX Query
DEFINE VAR __DS0FilterTable = 
  TREATAS({"Computers"}, 'Campaign'[Campaign])

EVALUATE
  TOPN(
    502,
    SUMMARIZECOLUMNS(
      ROLLUPADDISSUBTOTAL(
        ROLLUPGROUP('DimCall'[AgentName], 'DimCall'[Campaign], 'DimCall'[Company]), "IsGrandTotalRowTotal"
      ),
      __DS0FilterTable,
      "CountAgentName", CALCULATE(COUNTA('DimCall'[AgentName]))
    ),
    [IsGrandTotalRowTotal],
    0,
    'DimCall'[AgentName],
    1,
    'DimCall'[Campaign],
    1,
    'DimCall'[Company],
    1
  )

ORDER BY
  [IsGrandTotalRowTotal] DESC,
  'DimCall'[AgentName],
  'DimCall'[Campaign],
  'DimCall'[Company]


// SQL Query

SELECT 
TOP (1000001) [t0].[AgentName],[t0].[Company],
COUNT_BIG([t0].[AgentName])
 AS [a0]
FROM 
(
(select [$Table].[CallId] as [CallId],
    [$Table].[CallDate] as [CallDate],
    [$Table].[AgentName] as [AgentName],
    [$Table].[Company] as [Company],
     [$Table].[CRMCaseID] as [CRMCaseID]
from [dbo].[DimCall] as [$Table])
)
 AS [t0]
WHERE 
(
[t0].[Campaign] = N'Computers'
)

GROUP BY [t0].[AgentName],[t0].[Company] 


//DRIL THRU USING CAMPAIGN FROM MAIN PAGE

// DAX Query
DEFINE VAR __DS0FilterTable = 
  TREATAS({"Cedric Daughtery"}, 'Agent'[Agent])

EVALUATE
  TOPN(
    502,
    SUMMARIZECOLUMNS(
      ROLLUPADDISSUBTOTAL(
        ROLLUPGROUP('DimCall'[AgentName], 'DimCall'[Campaign], 'DimCall'[Company]), "IsGrandTotalRowTotal"
      ),
      __DS0FilterTable,
      "CountAgentName", CALCULATE(COUNTA('DimCall'[AgentName]))
    ),
    [IsGrandTotalRowTotal],
    0,
    'DimCall'[AgentName],
    1,
    'DimCall'[Campaign],
    1,
    'DimCall'[Company],
    1
  )

ORDER BY
  [IsGrandTotalRowTotal] DESC,
  'DimCall'[AgentName],
  'DimCall'[Campaign],
  'DimCall'[Company]


// SQL Query

SELECT 
TOP (1000001) [t0].[Campaign],[t0].[Company],
COUNT_BIG([t0].[AgentName])
 AS [a0]
FROM 
(
(select [$Table].[CallId] as [CallId],
     [$Table].[CallDate] as [CallDate],
    [$Table].[AgentName] as [AgentName],
    [$Table].[Company] as [Company],
     [$Table].[CRMCaseID] as [CRMCaseID]
from [dbo].[DimCall] as [$Table])
)
 AS [t0]
WHERE 
(
[t0].[AgentName] = N'Cedric Daughtery'
)

GROUP BY [t0].[Campaign],[t0].[Company] 

 

clear filter and grouping are sent back to SQL server and only the required result is returned to the report. I hope this answered your question and provided the clarification you need.

 

I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.

 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.