Forum Discussion
Drill through using inactive relationship
I have a client dataset structured as follows
| Client Name | Status | Active Date | Inactive Date |
| Alex | Active | 05/02/2024 | |
| Bella | Inactive | 18/06/2023 | 12/01/2024 |
| Chris | Active | 22/09/2022 | |
| Diana | Blank | 14/03/2024 | |
| Ethan | Inactive | 09/07/2021 | 23/12/2023 |
| Fiona | Active | 01/11/2023 | |
| George | Inactive | 27/04/2022 | 05/09/2024 |
| Hannah | Blank | 30/08/2024 | |
| Ian | Active | 16/05/2023 | |
| Julia | Inactive | 11/12/2023 | 19/02/2024 |
| Kevin | Active | 02/07/2024 | |
| Laura | Blank | 10/10/2024 | |
| Michael | Inactive | 03/09/2021 | 15/11/2023 |
| Natalie | Active | 20/04/2023 | |
| Oliver | Inactive | 29/05/2020 | 08/03/2023 |
I connected this table to a date table, establishing an active relationship between the date column in the date table and the active date column in the client table. Additionally, I set up an inactive relationship between the date table and the inactive date column in the client table.
Now, I want to create a bar chart in Power BI to visualise the number of active versus inactive clients per month over time in my main sheet.
Drillthrough Setup:
I have a drillthrough sheet that contains a table identical to my dataset.
The goal is:
When I drill through from active clients, I should see details of all active clients.
When I drill through from inactive clients, I should see details of all inactive clients.
Approach Taken:
I created a calculated column called Drillthrough, setting it to 1.
I then created a measure to count the number of 1s when the inactive relationship is active:
DAXCopyEditDrillthroughControl = CALCULATE(SUM(ClientTable[Drillthrough]), USERELATIONSHIP(ClientTable[Inactive Date], DateTable[Date]))In my bar chart:
I dragged Client Name to the Y-axis and applied a distinct count to avoid counting duplicates.
I added Status to the legend.
In the filter pane, I placed DrillthroughControl and set it to be greater than 0.
In my drillthrough table:
I added Client Name and Status to the drillthrough filters pane.
I added DrillthroughControl to the table filters pane, setting it to be greater than or equal to 1.
Issue:
When I drill through from Active clients, I do not get the expected results.
When I drill through from Inactive clients, I also do not get the correct values.
Request:
Could someone help me troubleshoot this issue or suggest an alternative approach to achieve the correct drillthrough functionality?
13 Replies
Hi Nabil20_24
You can try this method where you don't have to create any DAX measure for your drill-through page-Step 1: Modelling-
Establish an active relationship between date from Date table to Active Date from Client Table.
Step 2: Visualization
1. Use Status field on x-axis.
2. Count distinct Client name on y-axis
3. Status as Legend
4. You can use Year and month as slicer to filter for any specific year or monthStep 3: Set drill-through page
1. Use status as a drill-through filter.
2. Add a table with fields like Client name, Status, Active date. Inactive date.If you need more information on how drill-through works, you can visit
https://learn.microsoft.com/en-us/power-bi/create-reports/desktop-drillthrough
Hope this helps!
If the above information helps you, please give us a Kudos and marked the Accept as a solution.
Best Regards,
Community Support Team _ C Srikanth.That's brilliant, thanks a lot! However, I want to create a bar chart where the months are on the X-axis, and the number of active and inactive clients are displayed side by side. Also, to find inactive clients I need to use inactive date hence why I also need another relationship between date and inactive date 🙂
Hi Nabil20_24
You can have Month from date hierarchy on x-axis and drill through active/inactive users.
Make sure drill through page has Month as its drill through field.
If the above information helps you, please give us a Kudos and marked the Accept as a solution.
Best Regards,
Community Support Team _ C Srikanth.
Hi Nabil20_24
I'm not entirely sure what you're trying to achieve since you haven't provided the expected result, just descriptions based on the sample data. However, for active clients, shouldn't the date range be from the start of their activity until just before inactivity, rather than only the active date? As for inactive clients, do you want to count them only in the month they became inactive or continue counting them from that point onward?
You might find the attached pbix useful.
You're right! I should have included the expected output. To count the number of active clients, we need to count the number of client names where the status is 'Active'. To count inactive clients, we need to count the number of client names where the inactive date is not blank.
For example, if I have the dataset below and select the year 2022 from the slicer, the bar chart should show 2 active clients in January 2022 and 0 inactive clients. If I select 2023 from the slicer, In Jan it should show 3 inactive clients and 1 active client
Client Name Status Active Date Inactive Date Oliver Inactive 01/01/2022 08/01/2023 Ethan Inactive 02/01/2022 23/1/2023 Michael Inactive 03/01/2022 15/1/2023 George Inactive 04/01/2022 05/09/2024 Chris Active 01/01/2022 Natalie Active 02/01/2022 Ian Active 03/01/2023
Hi Nabil20_24
You can perform following transformations to achieve this result.
- Unpivot Active Date and Inactive Date
- Extract Active/Inactive text from Attribute column to create another status column
-
Add a flag that would filter out the duplicate records.
Specifically, those who are inactive but have active date. So, we remove them and categorize it as inactive.Use custom column to create flag column
Finally, filter out 0s and keep 1s to load the data.
5. Pull
Month from Date – X-axis
Count Client – Y axisStatus2 – Legend
Use slicer – Year (from date table), Month(From date table)
Modelling-
Active date--------Date[Date]
Then you will get the desired result.
For 2023For 2022
Hope this helps!
Attached file for your reference.
- Unpivot Active Date and Inactive Date
Hi Nabil20_24 ,
Just wanted to check if you had the opportunity to implement the solutions provided by the users?
If the response has addressed your query, please Accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you.Hi, thanks a lot for your time and help! The solution provided doesn’t quite get the output I need.
I want to create a bar chart that shows the number of active and inactive clients side by side. I also want to have one drill-through page that contains the underlying data.
So, when I right-click on the active bar in the chart (to drill through), I want to see all the active clients in detail, including their name, location, join date, etc. Similarly, when I click on the inactive bar, I want to go to the same drill-through sheet and see all the inactive clients without having to create separate drill-through pages or data tables for each client status (active and inactive).
Hi Nabil20_24 ,
The chart shows active/ inactive users side by side.
If you right click on the bar you would be directed to a table that will show the details of that particular bar.
Below snap for reference-If you need some help on how to implement drill through feature, you can refer this link
https://learn.microsoft.com/en-us/power-bi/create-reports/desktop-drillthroughHope this helps!
If the response has addressed your query, please Accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you.
Hi Nabil20_24 ,
Just wanted to check if you had the opportunity to review the suggestions provided?
If the response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Please feel free to contact us if you have any further questions.
Thank youHi @Nabil20_24 ,
Just wanted to check if you had the opportunity to review the suggestions provided?
If the response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Please feel free to contact us if you have any further questions.
Thank youHi @Nabil20_24 ,
Just wanted to check if you had the opportunity to review the suggestions provided?
If the response has addressed your query, please accept it as a solution ' so other members can easily find it.
Please feel free to contact us if you have any further questions.
Thank you
