Forum Discussion
Need help with drillthrough
Hi DarshanKumar ,
Thank you for your clarification of the data model. Great to hear it that you are doing it in the scalable standard way. In order to show the entire headcount for the all the companies while you are using drill through, I recommend you to create a disconnected table for the field you use in the visual. I am demonstrating this using my own headcount dashboard and use the department data to demonstrate the solution. First you can create a disconnected department table like below:
Drill_Department_Selector = VALUES('EmployeeFact'[Department])Then, you can use the keep this dimension table disconnected from your fact table calculating the headcount.
Then, instead of using the department field from your fact or related dimension table, use the disconnected department field from the table.
Headcount measure, as you know can be flexibly written like below:
Headcount = SUMX ( EmployeeFact, IF ( EmployeeFact[Start] <= [SelectedDate] && EmployeeFact[End] >= [SelectedDate], 1, BLANK () ) )But using the disconnected department field with this headcount measure will show all the unfiltered data, so wrap up this measure in a treatas related measure like below:
Headcount disconnected = CALCULATE([Headcount], TREATAS(VALUES('Drill_Department_Selector'[Department]), 'EmployeeFact'[Department]))The 1st page will look like above. Then you can add the usual headcount measure without treat as fixing and department with related dimension or directly from the fact table, and in the 2nd table showing the filtered data, you can add a measure like below and set the value to 1 and apply to get the appropriate filtering.
IsSelectedDepartment = VAR TargetDept = SELECTEDVALUE('Drill_Department_Selector'[Department]) VAR CurrentRowDept = SELECTEDVALUE('EmployeeFact'[Department]) RETURN IF( ISBLANK(TargetDept) || TargetDept = CurrentRowDept, 1, 0 )I hope this has addressed your issue. I am attaching an example pbix file for your reference.
Best regards,
DataNinja777 my headcount numbers are correct. I have a employee table and a disconnected date table.
My issue is withing the same drillthroug page im looking at showing the complete company details and also the details for each organization level(Exco,Exco-1,Exco-2....). My drill through works correctly when I drill through from the visual from one of the org level, but since in the visual we are not showing complete company HC , Im not able to drill through to show the complete HC details in drillthrough page.
Regards,
Darshan
Hi DarshanKumar ,
Thank you for your clarification of the data model. Great to hear it that you are doing it in the scalable standard way. In order to show the entire headcount for the all the companies while you are using drill through, I recommend you to create a disconnected table for the field you use in the visual. I am demonstrating this using my own headcount dashboard and use the department data to demonstrate the solution. First you can create a disconnected department table like below:
Drill_Department_Selector = VALUES('EmployeeFact'[Department])Then, you can use the keep this dimension table disconnected from your fact table calculating the headcount.
Then, instead of using the department field from your fact or related dimension table, use the disconnected department field from the table.
Headcount measure, as you know can be flexibly written like below:
Headcount =
SUMX (
EmployeeFact,
IF (
EmployeeFact[Start] <= [SelectedDate]
&& EmployeeFact[End] >= [SelectedDate],
1,
BLANK ()
)
)But using the disconnected department field with this headcount measure will show all the unfiltered data, so wrap up this measure in a treatas related measure like below:
Headcount disconnected = CALCULATE([Headcount], TREATAS(VALUES('Drill_Department_Selector'[Department]), 'EmployeeFact'[Department]))
The 1st page will look like above. Then you can add the usual headcount measure without treat as fixing and department with related dimension or directly from the fact table, and in the 2nd table showing the filtered data, you can add a measure like below and set the value to 1 and apply to get the appropriate filtering.
IsSelectedDepartment =
VAR TargetDept = SELECTEDVALUE('Drill_Department_Selector'[Department])
VAR CurrentRowDept = SELECTEDVALUE('EmployeeFact'[Department])
RETURN
IF(
ISBLANK(TargetDept) || TargetDept = CurrentRowDept,
1,
0
)
I hope this has addressed your issue. I am attaching an example pbix file for your reference.
Best regards,