Use DAX Measure to filter current page, drillthrough too

[ianpro]

I have a fact table that's comprised of patient accounts. I've created a measure that counts a subset of those records based on a specific patient registration type (categorized is IN (inpatient) or INO (observation).  My measure counts IN patients using this formula: 

OBS to IN = CALCULATE (
COUNTX(fPatientAccount,[AccountNumber]), -- counts account numbers
FILTER (fPatientAccount, fPatientAccount[RegType] = "IN") -- with Reg Type of IN
)

However when I use this measure on a visual, when I select it (in a matrix, for example, pictured below), it doesn't filter the other visuals on the same page. Nor when I use it as a drill-through source to a patient detail page does it work - it always returns the total number of patients (OBS Patients) , never the subset returned by the OBS to IN measure.  

Month	OBS Patients	OBS to IN
Jan	179	28
Feb	162	31
Mar	151	29

I'm sure this has something to do with the evaluation context, but I can't suss it out. I've looked at using ALL, ALLSELECTED, KEEPFILTERS, etc., but I'm struggling to understand what's happening. I'd like to avoid using a visual or page level filter to achieve the desired result. Can't a measure be used as a filter, without creating a slicer?  I've also tried using the edit interactions option between visuals but that has no effect either.

Thank you for any guidance.

[ianpro · Accepted solution]

Just for the record for the forum moderators, my original question doesn't really have a solution, only workarounds. For example, a visual like a card that displays a measure that includes a filter can't be used for true drill-through, but my workaround was to create a transparent button as an overlay on the card and have it navigate to a detail page that contains the same filters as the measure. This works fine for a small number of measures, but yes would be cumbersome for more than 5-6 from a user perspective, nevermind having to create many detail pages with only one specific purpose.

[sjoerdvn]

Yes, that's just the way measures work.

There are alternatives, for instance you could remove that filter on regType from that measure and instead apply that filter to the page or the visuals where you need them. If you want to display that other value in there, ignoring the filter, you can modify that measure and expand the context using ALL(

fPatientAccount[RegType]).
Also, for best performance use DISTINCTCOUNT(fPatientAccount[AccountNumber]) instead of that COUNTX. That is if you want to have the distinct number of accounts, because COUNTX doesn't give you that....

[ianpro]

---

@sjoerdvn wrote:

Also, for best performance use DISTINCTCOUNT(fPatientAccount[AccountNumber]) instead of that COUNTX. That is if you want to have the distinct number of accounts, because COUNTX doesn't give you that....

---

Thanks. I'm not using DISTINCTCOUNT since my fact table granularity is already 1 row per account. In this case, is there still any benefit to using DISTINCTCOUNT over COUNTX?

[sjoerdvn]

Yes. In this case you might also use COUNT. Try to avoid any X functions unless really necessary, because they will cause a loop executing an expression over all the rows in the table expression. If this is done over a large fact table, it will be slow.

[ianpro]

Nope. Returns the identical result (as expected) and behaves the same way.

A quote from a Marco Russo blog on SQLBI.com: Power BI accepts measures as filters only at a single visual level, and it does not accept measures as filters at the page level nor at the report level.

Does this apply to my case? Since my measure contains filtered data (as opposed to an aggregate amount working over all the records in the table), is it not possible to use it as a filter on other visuals and for drill-through?

[aduguid]

Try this measure

OBS to IN = CALCULATE (DISTINCTCOUNT(fPatientAccount,[AccountNumber]), fPatientAccount[RegType] = "IN")