Forum Discussion
Drill through using inactive relationship
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 month
Step 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.Thnak you; to count the number of inactive clients we need to use the inactive date; the data model you provided me with has active relationship between date from Date table to Active Date from Client Table only so it wont show me the results I want.
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