How to count values that appear in two tables

[Anonymous]

I can't provide actual data but I have two queries/tables I'm trying to compare. Table 1 has a bunch of IDs and services provided to those IDs. These IDs can and do show up multiple times (multiple rows) in Table 1 since services occured on different dates over time. Table 2 contains a list of IDs that are considered "current" and each ID appears only once in this table. I want to calculate a rate of how many current IDs have had services, i.e. the number of current ID's from Table 2 that are present in Table 1 / the total number of current IDs in Table 2.

I've tried to create a calculation that filters Table 1 to only the IDs that are also present in Table 2, determine the distinct count of those IDs, and then divide that by the total count of IDs in Table 2 but can't get it to work. Not sure if I should be using a calculated column in one the tables or creating a measure. The ID colums in both tables are setup to have a one to many relationship.

Thanks so much!

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure how your desired outcome looks like, but I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file.

Expected measure: = 
VAR _IDcount =
    COUNTROWS (
        SUMMARIZE ( FILTER ( 'ID', 'ID'[ID] IN VALUES ( Data[ID] ) ), 'ID'[ID] )
    )
VAR _ALLIDcount =
    COUNTROWS ( VALUES ( 'ID'[ID] ) )
RETURN
    DIVIDE ( _IDcount, _ALLIDcount )