Forum Discussion

lavankumar1989a's avatar
lavankumar1989a
Helper II
1 year ago
Solved

Enable conditional drillthrough

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 ...
Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    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

anmolmalviya05's avatar
anmolmalviya05
Super User
1 year ago

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's avatar
    lavankumar1989a
    Helper II
    1 year ago

    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