cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper III
Helper III

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

Accepted Solutions

@MohanV 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.

 






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
Super User IV
Super User IV

@MohanV ,

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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Super User III
Super User III

Hi @MohanV 

 

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

 

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

 

 

@MohanV 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.

 






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

Helpful resources

Announcements
November Update

Check it Out!

Click here to read more about the November 2020 Updates!

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform October Community Highlights

Check out the top community contributors across all of the communities

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors