Pie chart based on Selected Value with Drill Through

[SWR]

I have a Table: People with PeopleKey, LicenceExpiryDate, NextMedicalDate and NextEyeTestDate. I need to create a Pie Chart for each of these dates depending on the status: OK, Due or Overdue based on the selected Month. If the month is Sep 2024, then I compare the validity against the last of the selected month, i.e., 30-Sep-2023. Each Pie chart should also have drill through to the corresponding people.

I tried to create a measure (and similarly for other 2 dates status)

LicenceStatus =

VAR SelectedDate = MAX('Calendar'[Date])

VAR DaysForLicenceExpiry = DATEDIFF(SelectedDate, SELECTEDVALUE(People[LicenceExpiryDate]),DAY)

RETURN

SWITCH(TRUE()

        , ISBLANK(DaysForLicenceExpiry), "Overdue"

        , DaysForLicenceExpiry < 0, "Overdue"

        , DaysForLicenceExpiry >= 0 && DaysForLicenceExpiry <= 30, "Due"

        , DaysForLicenceExpiry > 30, "OK"

    )

Then I created another 3 measures each for OK, Due and Overdue:

#PeopleLicenceDue =

CALCULATE( COUNTROWS(People)

            , FILTER(People, [LicenceStatus] = "Due")

)

I also created the Pie chart.
But I am struggling to get the Drill through working

Thanks!

[ahadkarimi]

Hi @SWR, try this measure below, and if you encounter any issues, let me know.

Modify your LicenceStatus measure:

LicenceStatus =
VAR SelectedMonthEnd = EOMONTH(MAX('Calendar'[Date]), 0) -- Get the last day of the selected month
VAR DaysForLicenceExpiry = DATEDIFF(SelectedMonthEnd, People[LicenceExpiryDate], DAY)
RETURN
SWITCH(
    TRUE(),
    ISBLANK(People[LicenceExpiryDate]), "Overdue",
    DaysForLicenceExpiry < 0, "Overdue",
    DaysForLicenceExpiry >= 0 && DaysForLicenceExpiry <= 30, "Due",
    DaysForLicenceExpiry > 30, "OK"
)

Measures for pie chart statuses:

#PeopleLicenceDue =
CALCULATE(
    COUNTROWS(People),
    FILTER(People, [LicenceStatus] = "Due")
)

Did I answer your question? If so, please mark my post as the solution! ✔️
Your Kudos are much appreciated! Proud to be a Solution Specialist!

[SWR · Accepted solution]

Hi AhadKarimi,

Thanks for your reply. I had got the pie chart and measures already. But I was not able to drill through from this pie chart to another page which holds the Table for People. For examply, if I select LicenceDue in the pie chart and then drill through, I am expecting to see only those People with LicenceStatus Due. But it was showing all records.

I have come up with a work-around for this by creating separate pages for LicenceDue, LicenceOK and LIcenceOverdue with tables and filtered to the respective Licence Status. It seems to be working.

Once again, thanks for your time and support. Appreciate it.

SWR.

Regards, SWR