cancel
Showing results for 
Search instead for 
Did you mean: 
yingyinr

Several factors influencing Drill through feature in Power BI

Scenario:  

When we perform the “Drill through” function in the Power BI, we will left-click or right-click on the data points in the data visual according to personal habits. In the Power BI Desktop and Power BI Services, we drill through by clicking the right mouse button by default. If you need to click on the left mouse button to generate, you need to open some components to assist in the formation of Drill through, and the generation of Drill through will also be related to the date format. This blog describes the following steps:

  1. By opening the relevant components, we can open the Drill through button in Power BI Desktop/Power BI Services with the left mouse button.
  2. During the Drill through operation, the Drill through button will not appear due to the inconsistency between the date hierarchy and the date field.

Sample Data:  

AllDates table, which contains dates from 2021.01.01-2021.01.7, member and amount.

yingyinr_0-1623748735406.png

Event table, which contains group and member.

yingyinr_1-1623748735407.png

 

Expected output: 
1. In Power BI Desktop, we can click the left mouse button to display Drill through.

yingyinr_2-1623748805887.png

 

Get the same result in Power BI Services.

yingyinr_3-1623748805888.png

2. When the date hierarchy is modified to the [Date] column, Drill through appears.

yingyinr_4-1623748805891.png

Operations: 

Power BI Desktop/Power BI Services:

1. Create visual objects in Page1 and Page2 through two tables.

yingyinr_6-1623748805893.pngyingyinr_13-1623749079037.png

2. Place the [member] column in the Event table in Page1 in the Visualizations – Fields – Drill through – Add drill fields here column.

Here are some functions included:

  • Keep all filters: If you open this option in the original page, the navigated page will also be filtered by the applied filters in the original page when it’s being drilled through.
  • Summarized: When you add a measure or summarized numeric column, you can drill through to the page when the field is used in the Value area of a visual.
  • Used as a category: When you add the column as the “Don’t summarize” type in the visual of the original page, it can be drilled through to the linked page.

         yingyinr_8-1623748969660.png

3. In the visual object of Page2, click the left mouse button and find that there is no Drill through.

 

4. In the Power BI Desktop, select the visual object of Page2, and click the Drill through of Data/Drill in the toolbar above.

yingyinr_9-1623748969663.png

 

5. Click the visual object on Page2 with the left mouse button and find that Drill through appears.

yingyinr_10-1623748969665.png

 

6. We got the desired result in Power BI Desktop, and we published it to Power BI Services. There was a strange situation! When I click on the left mouse button, the Drill through function can’t work. After research, I found that the button will not be synchronized from Power BI Desktop to the Power BI service, we need to open it manually.

 

7. Click on the visual object in the figure, select Explore in the toolbar above, and select the Drill through button as On.

yingyinr_11-1623748969666.png

 

 

8. Click the visual object on Page2 with the left mouse button and find that Drill through appears.

yingyinr_12-1623748969667.png

 

Step analysis:

We want to use the left mouse button to display Drill through in Power BI Desktop, we need to open the Drill through of Data/Drill. But publishing to Power BI Services will not be synchronized, we need to manually open the Drill through of Explore, so it will be implemented on Power BI Services.

 

Note:

Using hierarchical structure in Power BI Desktop, we can Click to turn on Drill down to display the next level of data.

This is the current graph, showing Year.

yingyinr_0-1623749733255.png

 

After clicking the left mouse button, display the next level month.

yingyinr_1-1623749733258.png

 

But after we open the Drill through of Data/Drill in Power BI Desktop, no matter whether it is Drill down or Drill through, we can no longer use it.

yingyinr_2-1623749733262.png

 

 

Modify the date hierarchy to [Date] column to display Drill through:

1. Create visual objects in Page1 and Page2 through two tables. The date in Page2 is a date hierarchy (Year, Quarter, Month, Day).       yingyinr_3-1623749798729.png           yingyinr_4-1623749798731.png

 

2. Place the [date] column in the All table in Page1 in the Visualizations – Fields – Drill through – Add drill fields here column. 

         yingyinr_5-1623749798731.png

 

3. Select the visual object in Page2, select the right mouse button, and there is no Drill through.

yingyinr_6-1623749798734.png

4. Change the date hierarchy of its Page2 visual object to [Date] column.

yingyinr_7-1623749798734.png

5. Select the visual object in Page2, right-click the mouse, and Drill through appears.

yingyinr_8-1623749798736.png

 

Step analysis:

When the date is in a hierarchical structure (Year, Quarter, Month, Day), Drill through will not appear, only if you change it to the exact time, it will appear.

 

Summary: 

The result obtained is the expected effect

1. In Power BI Desktop, by changing the Drill through in Data/Dill to ON, you can use the left mouse button to display Drill through.

yingyinr_0-1623751070144.png

 

After publishing it to Power BI Services, it will not be synchronized with it. We need to manually set the Drill through in Data/Dill to ON, and then the Drill through will appear.

yingyinr_10-1623749890781.png

 

2. When the date hierarchy is changed to [Date] column, Drill through appears.yingyinr_11-1623749890783.png

The result is exactly the same as I expected. Please refer to the attachment below for details. I hope this article can help you with the similar question.  

 

 

Author: Yang Liu 

Reviewer: Ula Huang, Kerry Wang

Polls
What is your favorite Power BI Feature release in July 2021?