Drill Through not returning correct values

[DarshanKumar]

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

[v-tejrama]

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]

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]

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.

Source

Employee ID

Employee No

Termination date

First Name

Last Name

Employment Status

Job Department

Job Title

Job Reporting From

Job 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 

[v-tejrama]

Hi @DarshanKumar,

Thanks for the detailed follow up and sharing the structure it really helped to understand your issue better.

I tried to reproduce the problem, and the drill-through duplicates are happening because each employee has multiple rows for job or department changes.

To fix this, I created a summarized table with only one row per employee using this DAX:

UniqueEmployees =
SUMMARIZE(
'EmployeeChanges',
'EmployeeChanges'[Employee No],
"First Name", MAX('EmployeeChanges'[First Name]),
"Last Name", MAX('EmployeeChanges'[Last Name]),
"Department", MAX('EmployeeChanges'[Job Department]),
"Status", MAX('EmployeeChanges'[Employment Status])
)

Then I used this UniqueEmployees table on the drill-through page and added Employee No in the drill-through field. Now, it shows only one clean row per employee and avoids duplicates.

If this still doesn't work for your case, feel free to share your sample data (with dummy values), and I’ll happily repro and share back the working steps or file.

Please find the attached .pbix file for your reference.

Thanks again!

Tejaswi.

[v-tejrama]

Hi @DarshanKumar ,

I wanted to follow up and see if you had a chance to review the information shared. If you have any further questions or need additional assistance, feel free to reach out.

Thank you.

[v-tejrama]

Hi @DarshanKumar ,

I hope the information provided has been useful. Please let me know if you need further clarification or would like to continue the discussion.

Thank you.

[DarshanKumar]

Hi @v-tejrama,

I tried the above unfortunately its not working. Im not able to upload the file, I have pasted the dummy data. The issue is I have two visuals, one  is simple water fall chart and other one bar chart showing individual department month end HC, both the visual using the same measure and there are two filter in the sheet one with date and other one with department. when I drill through from water fall chart with only date filter selected departments are not correct and in the drill through page , however when I drill thorugh from bar chart  the department is correct as the department filter is also flowing thourgh. Please can you help.

Regards,

Darshan 

[v-tejrama · Accepted solution]

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.

[v-tejrama]

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. If my response has addressed your query, please Accept it as a solution so that other community members can find it easily.

Thank you.

[v-tejrama]

Hi @DarshanKumar 

I hope the above details help you fix the issue. If you still have any questions or need more help, feel free to reach out. We’re always here to support you.

Thank you.

[DarshanKumar]

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

[Kishore_KVN]

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