Counting Events That Happen in Two Tables for Same Data Table

[datastrategy]

I have patients that were created in a surgery center and accounts that were created in a pharmacy nearby.  Want to know in a given month how many came through from the VW_Patients to the "accounts" (Pharmacy).  They are matched by the "chart number" to "Alternate Account Number".  I keep getting results that total each for a given month, but I want to know only those that matched with the VW_Patients in that month.  

Basically two columns with counting patients.  One ("Chart Number") has slightly more than "accounts" because some don't go to the pharmacy.  Want to then calculate the % that go to Pharmacy (accounts) for each month.

been looking all day and decided to post.  thanks!

[v-shex-msft · Accepted solution]

Hi @datastrategy ,

It seems like you want to calculate between two tables. I'd like to suggest you break cleandr table relationships and use formula to manually calculate correspond table count and do compare with them.

Measure =
VAR selected =
    VALUES ( Date[Date] )
VAR _account =
    CALCULATETABLE (
        VALUES ( Account[Alternate Account Number] ),
        FILTER ( ALLSELECTED ( Account ), [Date] IN selected )
    )
VAR _patient =
    CALCULATETABLE (
        VALUES ( VW_Patients[chart number] ),
        FILTER ( ALLSELECTED ( VW_Patients ), [Date] IN selected )
    )
RETURN
    DIVIDE (
        COUNTROWS ( INTERSECT ( _patient, _account ) ),
        COUNTROWS ( _account )
    )

Notice: create a table visual based on date dimension table year, month fields and above measure.

If above not help, can you please share some sample data for test, it is hard to coding formula without any sample data.(do mask on sensitive data)

How to Get Your Question Answered Quickly

Regards,

Xiaoxin Sheng