Forum Discussion
Helper IIDrill Through not returning correct values
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.
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
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.
DarshanKumarHi Anonymous,
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
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.
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.