Several factors influencing Drill through feature in Power BI

[v-yiruan-msft]

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.

Event table, which contains group and member.

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

Get the same result in Power BI Services.

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

Operations: 

Power BI Desktop/Power BI Services:

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

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.

         

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.

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

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.

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

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.

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

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.

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).                  

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

         

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

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

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

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.

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.

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

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

Several factors influencing Drill through feature in Power BI.pbix