Forum Discussion

DarshanKumar's avatar
DarshanKumar
Helper II
1 year ago
Solved

Drill Through not returning correct values

Hi,

 

Im trying to get the underlying data of the measure using drill thorugh. I have a measure called "Month End HC" which for the month of Jun25 returns 1787 on the visual, however when I check the data in the drill through details there are 1790 records. Im not sure what im doing wrong any help is highly appreciated. 

 

The second issue is, we have duplicates in our data  and the value returned by drill through is not exactly the same as  filtered measure. EX; Department is supposed to X but it returns Y which is not the value of the filtered condition.

 

Any help is highly appreciated.

 

Regards,

Darshan

Need Help
  • v-tejrama's avatar
    v-tejrama
    1 year ago

    Hi again DarshanKumar ,

     

    Thank you for your clear explanation of the issue. I fully understand the confusion. The core reason is that Power BI only sends filters to the drill-through page based on the fields present in the visual where you initiate the drill-through. When you use the bar chart, which includes the department field, Power BI passes the department filter as expected. However, in the waterfall chart, since the department isn’t included, it doesn’t pass that filter resulting in incorrect departments on the drill-through page.

     

    To address this effectively, I recommend creating a separate table for departments (a disconnected slicer) and building your visuals with a measure that references the selected department from this slicer. This ensures both the bar chart and waterfall chart respond to the slicer, regardless of whether the department field is present in the visual. On the drill-through page, include the same slicer and avoid adding the department field to the drill-through pane. This approach guarantees the department filter is always applied correctly, providing consistent and accurate results across all visuals and drill-through pages.

     

    Thank you,

    Tejaswi.

12 Replies

  • Kishore_KVN's avatar
    Kishore_KVN
    Solution Sage
    1 year ago

    Hello DarshanKumar ,

    May I know what are the fields used in the measure and the drill through. Hope you have used proper category and summarization in drill through?

     

    Thanks,

    Kishore

  • DarshanKumar's avatar
    DarshanKumar
    Helper II
    1 year ago

    Hi Kishore_KVN 

     

    Thanks for your response, please find below the measure

     

    Month End Headcount =
    VAR selectedmonth =
        MAX ( dim_calendar[Date] )
    RETURN
        CALCULATE (
            [Headcount(EmpID)],
            'curated rpt_hr_headcount'[headcountflag] = 1,
            'curated rpt_hr_headcount'[job_effective_from] <= selectedmonth,
            NOT ISBLANK ( 'curated rpt_hr_headcount'[job_effective_from] ),
            (
                ISBLANK ( 'curated rpt_hr_headcount'[job_effective_to] )
                    || 'curated rpt_hr_headcount'[job_effective_to] > selectedmonth
            )
        )
     
    In the Drill through details page, im using
     
    First Name Last Name Job Department
  • v-tejrama's avatar
    v-tejrama
    Community Support
    1 year ago

    Hi DarshanKumar ,

    Thanks for reaching out to the Microsoft fabric community forum.

     

    The issue you're facing isn’t something that needs to be fixed with DAX it's actually related to how your data model is structured. To resolve it properly and avoid inconsistent drill-through or mismatched row counts, here’s what you need to do:

     

    Start by creating a separate Departments dimension table. This table should contain just one row per department basically a unique list of departments. Think of it as your clean reference list.

     

    Once that’s ready, connect this dimension table to both of your fact tables using the common key, such as DepartmentID or whatever identifier you’re using to link departments.

     

    Then, in your drill-through page, make sure you use the Department field from the dimension table, not the one from your fact table. Drag this dimension-based field into your drill-through filters.

     

    Also, when you build your visuals charts, tables, or anything interactive use the Department field from the dimension table there too. This ensures that filters work consistently across the report and avoids issues like incorrect totals or missing data during drill-through.

     

    Once you do this, everything should behave as expected no extra DAX or complex logic required.

     

    Please find the attached .pbix file for your reference.

     


    Best Regards,
    Tejaswi.
    Community Support

     

    • v-tejrama's avatar
      v-tejrama
      Community Support
      1 year ago

      Hi DarshanKumar ,

       

      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.

       

      Thank you.

      • DarshanKumar's avatar
        DarshanKumar
        Helper II
        1 year ago

        Hi v-tejrama,

         

        Thanks for checking, its still not working. I will share more details.

         

        I have a table which has each record for every changes in the job, the sample table is mentioned below.

         

        SourceEmployee IDEmployee NoTermination dateFirst NameLast NameEmployment StatusJob DepartmentJob Title Job Reporting FromJob Reporting To

         

        There is one record for each of the job changes, like department changes and reportin changes.The measure mentione d above when I drill through  from the month end HC bar I have this duplicate issue, however I have one more chart in the same page which is showing month end HC by department, whenI drill through by each department the results are correct. 

         

        Please can you check and let me know whjat I am doing wrong.

         

        Regards,

        Darshan