Solved: Drilling doesn't work - Page 2

Anonymous · ‎08-10-2020

Original Post - https://community.powerbi.com/t5/Desktop/Monthly-Total-Open-Cases/mp/1284325#M561428

Can anyone advise how to get the following measurement to drill correctly?

In my previous post, a member successfully provided a qualifying metric (a month-to-month count of active users, with a start date and no end date, including those from previous months) See the context above.

The next step has me struggling again!

I understand that the drill-through feature is not possible with the current measure as we have a CALCULATE in the DAX and Power BI cannot use the complex measure to drill down. The first question I can take on from the measure created above will be "great, can I see who is active for each month, now I want to know, who are they?"

I'm trying to create a page where I can drill down to the list of PERSON_IDs that are in that month, from the measure created:

Active People (Active People)

WHERE maxdate?

MAX ('Calendar' [Date])

return

CALCULATE (

DISTINCTCOUNT (DM_PERIODS_OF_CARE [PERSON_ID]),

ALL ('Calendar' [Date]),

FILTER (

ALL (DM_PERIODS_OF_CARE),

DM_PERIODS_OF_CARE [START_DATE] <- maxdate

&& OR (

DM_PERIODS_OF_CARE [END_DATE]> maxdate,

ISBLANK (DM_PERIODS_OF_CARE [END_DATE])

)

I currently have a table on another page with Active Persons (which is the originally posted measure for the requirement) and PERSON_ID. It is not working however there are much more than 305 rows for the Aug 2020 exam as screenshot here:

Any suggestions on this?

Thank you very much,

Dan.

mahoneypat · ‎08-11-2020

Please try this measure added to your table visual on the drillthrough page. I reproduced your scenario on my end and this appears to get your desired functionality.

IsActive =
VAR mindate =
    MIN ( 'Date'[Date] )
VAR maxdate =
    MAX ( 'Date'[Date] )
RETURN
    CALCULATE (
        COUNTROWS ( Employees ),
        ALL ( 'Date' ),
        OR ( Employees[EndDate] >= mindate, ISBLANK ( Employees[EndDate] ) ),
        Employees[StartDate] <= maxdate
    )

If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.

Regards,

Pat

Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.

@mahoneypa HoosierBI on YouTube

View solution in original post