Need help with drillthrough

[DarshanKumar]

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 business is to show the details of the entire company in drillthrough page along with the each org level. Is there a way we can do it in the same drillthorugh page instead of creating an additional table/matrix view ?

Any help is highly appreciated.

Regards,

Darshan

[v-hjannapu]

Hi @DarshanKumar,

I would also take a moment to thank @DataNinja777  , @MFelix, @Chewdata for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.
 

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.

Regards,
Community Support Team.

[v-hjannapu]

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 are always here to support you.


Regards,
Community Support Team.

[DataNinja777]

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]

@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 · Accepted solution]

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,

[Chewdata]

Not sure if I completly understand your question. But might a second measure that uses the ALL() / REMOVEFILTER() in the calculation not yield the desired results?

All Years =

CALCULATE(

    [# cases],

    ALL(Datumtabel)

)

[DarshanKumar]

Thanks Miguel. Will check on this and confirm you if it works.

[MFelix]

Hi @DarshanKumar ,

When you refer that you want to show the entire companyin the drill trough do you mean you want to drill dow to org level and then be able to show the entire company information based on a button for example? Or do you want to have two visuals one that shows company and other the org level?

[DarshanKumar]

@MFelix  Thanks for your response.

When I drill down by org level it   shows the data of that org level, what I want is in the same drillthrough page  to also show the data of entire company.

Ex:

Company HC - 1960

Exco-1 HC - 280

Im able to see the 280 colleagues data in drill through page when I drillthrough but I also want to find a way to show the details of the 1960 colleagues data in the same drillthrough page.

Regards,

Darshan

[MFelix]

Hi @DarshanKumar ,

I assume that you want to start by checking the org information and the make a way to clear the filters of the org and return all of the information.

I have done a similar UX based on a bookmark button that would clear the data filter of the drill trough.

On the drill trough page be sure that you do not have any filters applied of the drill trough, you can do it by clearing all the filters that show on the filter pane:

No create a bookmark and be sure to leave the Data option on:

Add a button with the bookmark on that page then give it the proper name.

Now when you drill trough you can click that button and just clear the data:

[DarshanKumar]

@MFelix  in the filter pane of the drillthrough page, I have also added a condition "Measue=1" clearing the filters will result in HC numbers not being correct.

[MFelix]

Hi @DarshanKumar ,

What does dlthat measure does?

Can you explain a little bit better?