Drilldown Table Help

[Shawkins566]

Hi,

I am trying to create a drill down table.  I have a long list of projects, with their division and their KPI statuses (of which there are 10) across a 3-month period on a weekly basis.  I want my table to show the current week as standard, then I want the drill down to show me the history, so every other date prior to the current.

Is there a way to do this, I've tried to create a hierarchy using a column that determines if it is the current week or not but it's not something I've used before and it's not working for me.  

I've tried a few things I've seen online and tried copilot but still no luck.

Any help would be appreciated.

Many thanks

[v-kpoloju-msft · Accepted solution]

Hi @Shawkins566,
Thank you for reaching out to the Microsoft fabric community forum. Thank you @bhanu_gautam, for your inputs on this issue.
After reviewing the details you provided, I have identified few workarounds that may help resolve the issue. Please follow these steps:

Verify Date/Week Column Format: Ensure the date column or week identifier is correctly formatted (e.g., as a week number or date). The drill-down should rely on this column.

Establish a Week Hierarchy: When using tools like Power BI or Excel, create a date hierarchy with fields for the current week and past weeks. Ensure the week field is accurately categorized and ordered in the hierarchy (e.g., Current Week → Week 1 → Week 2 → etc.).

Implement DAX Measures for Filtering: Use a DAX measure to identify the current week. Example formula in Power BI:     IsCurrentWeek = IF('Date'[WeekStart] = TODAY(), 1, 0)
Apply this measure to conditionally format or filter the table so that the current week is displayed by default.

Enable Drill-Down Functionality: Ensure drill-down functionality is activated, typically by setting up a hierarchy (Project → Division → KPI → Week) and configuring the visual to allow users to click on a level (such as the current week) and drill down to previous weeks.

Correctly Sort Data: Ensure the data is sorted appropriately (e.g., by date, with the current week appearing first). This can be achieved using a calculated column or measure to sort by week number or date.

Test with Sample Data: Create a simplified version of the table with sample data for validation. Verify that the drill-down works as expected when clicking on the current week and navigating to past weeks.

Kindly refer to the below link for better understanding:
Drill mode in the Power BI service - Power BI | Microsoft Learn
Drillthrough, drilldown, subreports, and nested data regions in a Power BI paginated report - Power ...

If this post helps, then please give us ‘Kudos’ and consider Accept it as a solution to help the other members find it more quickly.

Thank you for using Microsoft Community Forum.

[v-kpoloju-msft]

Hi @Shawkins566,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[v-kpoloju-msft]

Hi @Shawkins566,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.

[bhanu_gautam]

@Shawkins566 Create a calculated column in your date table to determine if each date is in the current week or not. 

IsCurrentWeek = IF(WEEKDAY('Date'[Date]) = WEEKDAY(TODAY()), "Current Week", "Historical Week")

In your table visual, use the "IsCurrentWeek" calculated column as a hierarchy. This will allow you to drill down to view historical data for dates in the "Historical Week" category.

[Shawkins566]

I did that but I didn't get a drill down icon appear when I tried.  Does this need to be enabled in some way?