Drilling doesn't work

[Anonymous]

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]

You should be able to drill through on your month column.  Also, please try the modified expression.  It now only removes the filters from the date columns instead of from the whole table (including Person_ID).  It should return 1 for each active employee in the date range in scope.

Active Persons =

VAR maxdate =

MAX ( 'Calendar'[Date] )

RETURN

CALCULATE (

DISTINCTCOUNT ( DM_PERIODS_OF_CARE[PERSON_ID] ),

ALL ( 'Calendar'[Date] ),

FILTER (

ALL ( DM_PERIODS_OF_CARE[START_DATE], DM_PERIODS_OF_CARE[END_DATE], ),

DM_PERIODS_OF_CARE[START_DATE] <= maxdate

&& OR (

DM_PERIODS_OF_CARE[END_DATE] > maxdate,

ISBLANK ( DM_PERIODS_OF_CARE[END_DATE] )

)

)

)

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

Regards,

Pat

[Anonymous]

Hi @mahoneypat,

Unfortunately that does not work, it creates the same problem as in my original post. It filters the Active Persons for that month only, when what is needed is the count of active persons that have a start date and no end date that are active in that month and every month where they are active (as your original measure successfully does) e.g:

person 1 has a start date in Jan 2020 and no end date, therefore needs to be counted in Jan, Feb, Mar, Apr, May, Jun, Jul, Aug. 

person 2 has a start date in Mar 2020 and no end date, therefore needs to be counted in Mar, Apr, May, Jun, Jul, Aug.

person 3 has a start date in Mar 2020 and an end date in Jun 2020, therefore needs to be counted in Mar, Apr, May, Jun.

The next step is to be able to drill-through on that month (on the bar chart visualisation) and see who those active persons are for that month (in a table visualisation on another page)

Please see my post as I have edited it for clarity. The bar chart shows the correct numbers, however when I drill-through to a page with a table with persons on, it does not work.

Thanks again for your assistance Pat,

Dan.

[mahoneypat · Accepted solution]

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

[Anonymous]

hi @mahoneypat 

this does not work either.

when i drill-through on a month, July2020 for example with 312 active persons, we drill-through to the page where the table shows list of persons with 'IsActive' measure under the 'Drill through' on the visualisation. It does not work because there is over 30,000 rows. This does not make sense when there are 312 active persons in the month.

Any ideas?

I appreciate your continued effort.

Dan.

[mahoneypat]

The IsActive measure worked on my mockup file.  Can you provide a pbix file that replicates what you are seeing and share a link to it?  Not sure what else to try without that.

Regards,

Pat

[Anonymous]

@mahoneypat thanks so much, I started all over again and it works!! 

Whatever trouble I was having before isn't there now. All working 😎

[mahoneypat]

Been there.  Glad it's working.

Regards,

Pat

[amitchandak]

@Anonymous , as of now you can only drill on dimension/column values

[Anonymous]

@amitchandak  I am trying to drill-through the bar chart visualisation, so that it shows the applicable PERSON_ID that is being counted for that month. For some reason the original bar chart was removed from my post, I have added it again.

[v-xuding-msft]

Hi @Anonymous ,

Your image shows that you have added a meausre into drill-through field. So this page is a detailed one. You need to click the values from another page then it will jump to this page.

https://docs.microsoft.com/en-us/power-bi/create-reports/desktop-drillthrough

[Anonymous]

@v-xuding-msft this is the original visualisation:

I want to drill-through by the Month Date and see the list of active persons and see the list of active persons, which is not working as below (it is returning all PERSON_ID instead of relevant active persons for the month)