Forum Discussion

DarshanKumar's avatar
DarshanKumar
Helper II
8 months ago
Solved

Need help with drillthrough

Hi,   I have a requirement where Im showing the headcount by organization level and have set up the drillthrough page to show the details of the each org level.  The additional requirement from the...
Need Help
  • DataNinja777's avatar
    DataNinja777
    8 months ago

    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's avatar
DataNinja777
Super User
8 months ago

Hi DarshanKumar ,

 

Regarding your question, you have not described your data model, but in order for you to have a proper headcount analysis, you will need to have the data model which utilizes disconnected calendar table and the employee table with employee ID, employee joining date and employee leaving date.  Without proper data model set up for the headcount, the analysis of the headcount will be static data your precalculated headcount data on a specific date.  Please could I clarify the structure of your data model so that we can think of what to fix first before the drill-through issue you've highlighted?  

 

Best regards,

 

  • DarshanKumar's avatar
    DarshanKumar
    Helper II
    8 months ago

    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

    • DataNinja777's avatar
      DataNinja777
      Super User
      8 months ago

      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,