Enable conditional drillthrough

[lavankumar1989a]

Hi Expert,

Hope you are doing great! i need you guidence to achive my requirement in PowerBI, i have bunch of slicers in my first page and a table visual and if the user selected a slicer called Year as 2024 in my report page 1, when user want to see the details to navigate to drillthrough he wants to see both Year 2024 and it's previous year 2023 data as well and it should be dynamic like if user selcted as slicer as 2021 and in drill through has to see 2021 & 2020 data.

So please share solutions with the sample pbis file pls and in my fact table i don't have date columns and i have only Year as a column to keep in slicer.

Please do let me know if any other details needed and thank you so much for your kind support!

Thanks,

Lavan

[anmolmalviya05]

Hi , Hope you are doing good!

Hi there! I’d be happy to assist with your Power BI requirement. To display data for both the selected year and the previous year on a drillthrough page, here’s what you need to do:

Step 1: Set Up a Drillthrough Page

Create a new page in your Power BI report and configure it as a drillthrough page.

Add the visuals you want for detailed data analysis.

Step 2: Add a Year Slicer

Insert a slicer for the Year column on the drillthrough page so that users can filter data by year.

Step 3: Create a Measure for the Previous Year

Define a DAX measure to calculate the previous year dynamically based on the selected year:

PreviousYear = SELECTEDVALUE('YourTable'[Year]) - 1

Step 4: Configure Drillthrough Filters

Drag the Year column to the Drillthrough Filters pane.

Add the PreviousYear measure to the Drillthrough Filters pane to ensure the page displays data for both years.

Step 5: Apply Conditional Logic

Use a DAX formula to create a calculated column that identifies the selected year and its previous year:

CombinedYears =

VAR SelectedYear = SELECTEDVALUE('YourTable'[Year])

VAR PriorYear = SelectedYear - 1

RETURN

IF('YourTable'[Year] = SelectedYear || 'YourTable'[Year] = PriorYear, 1, 0)

Filter the visuals on the drillthrough page using this new column to limit data to these two years.

Step 6: Enable Drillthrough Navigation

On the main report page, ensure the drillthrough feature is activated for the Year column.

Users can right-click on a slicer or visual and select Drillthrough to navigate to the detailed page, which will display data for the selected year and the previous year.

This approach ensures your drillthrough dynamically adapts to the selected year, showing relevant data without requiring manual updates. If you face any challenges or need more details, don’t hesitate to ask!

Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!

Let's Connect on LinkedIn: https://www.linkedin.com/in/anmol-malviya/?originalSubdomain=in

Subscribe my youtube channel for Microsoft Fabric and Power BI updates: https://www.youtube.com/@AnmolPowerBICorner

@lavankumar1989a

[lavankumar1989a]

Thank you so much @123abc , @anmolmalviya05  for your kind response and the detailed steps shared.

I tried but unable to meet the expectations and while drillthrough it's not working as expected. So please provide the sample pbix file if possible to implement the steps as expected.

Just to give more insights on my model, i do have 2 fact tables like Table_A & Table_B and join between the both the tables are many-to-many and fist page si developed using columns using Table_A and along with the slicers also from Table_A.

Drillthrough page is developed using Table_B but we enabled sync slicer option to react filters applied in the first page should pick the respective details info in the another page 2. But here when user selects year as 2024, in another page user have to see details for 2024 & 2023 based on the filters that they applied in the first page via drillthrough option.

Please do let me know if any clarifications needed and do the needful. thanks once again.

Thanks,

Lavan

[v-yaningy-msft · Accepted solution]

Hi, @lavankumar1989a 

Thanks for the reply from Uzi2019, anmolmalviya05 and 123abc. You can put the measure to Filters pane to enable filtering.

Flag = 
VAR _slicer =
    SELECTEDVALUE ( Slicer[Year] )
VAR _startYear = _slicer - 1
VAR _tableYear =
    SELECTEDVALUE ( 'Table'[Year] )
VAR _result =
    IF (
        ISFILTERED ( Slicer[Year] ),
        IF ( _tableYear >= _startYear && _tableYear <= _slicer, 1 ),
        1
    )
RETURN
    _result

No relationship between the two tables.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[Uzi2019]

hi @lavankumar1989a 
check these video for better understanding.

https://www.youtube.com/watch?v=O7aL5v1r6Q4

https://www.youtube.com/watch?v=Z7SBnMnD-1s

https://www.youtube.com/watch?v=OcZr_70OGPo

I hope I answered you question!

[123abc]

Hi there! I’d be happy to help you with your Power BI requirement. To achieve conditional drillthrough that dynamically includes the selected year and its previous year, you can follow these steps:

1. Create a Drillthrough Page:

   • Add a new page to your report and set it up as a drillthrough page.
   • Add the visuals you want to display on this page.

2. Add a Year Slicer:

   • On your drillthrough page, add a slicer for the Year column.

3. Create a Measure for Previous Year:

   • Create a measure to calculate the previous year based on the selected year. You can use DAX to achieve this:

     PreviousYear = SELECTEDVALUE('YourTable'[Year]) - 1

4. Add a Drillthrough Filter:

   • Add the Year column to the Drillthrough filters well on the drillthrough page.
   • Add the measure you created for the previous year to the Drillthrough filters well as well.

5. Set Up Conditional Filtering:

   • Use a DAX formula to create a new column that combines the selected year and the previous year. For example:

     CombinedYears = 
     VAR SelectedYear = SELECTEDVALUE('YourTable'[Year])
     VAR PreviousYear = SelectedYear - 1
     RETURN
     IF('YourTable'[Year] = SelectedYear || 'YourTable'[Year] = PreviousYear, 1, 0)

   • Use this new column as a filter on your drillthrough page to ensure it shows data for both the selected year and the previous year.

6. Enable Drillthrough:

   • On your main report page, ensure that the drillthrough functionality is enabled for the Year column. Users can right-click on the Year slicer and select Drillthrough to navigate to the detailed page.

Unfortunately, I can’t provide a sample .pbix file directly, but you can follow these steps to set it up in your own Power BI report. If you need more detailed guidance or run into any issues, feel free to ask!

Is there anything else you need help with?

OR PLEASE FOLLOW BELOW LINK ... I HOPE THIS WILL HELP YOU.

Solved: Is there a way to drill trough based on conditions - Microsoft Fabric Community

Set up drillthrough in Power BI reports - Power BI | Microsoft Learn

Use cross-report drillthrough in Power BI Desktop - Power BI | Microsoft Learn

Set up Drill-through Pages in your Power BI Reports | Zebra BI Knowledge Base

Power BI Desktop May 2020 Feature Summary | Microsoft Power BI Blog | Microsoft Power BI

I hope this helps!
If you found this answer helpful:

Mark it as the solution to help others find it faster.
Give it a kudo to show your appreciation!
Thank you for being an awesome community member!