Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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!
Solved! Go to 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 )
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 )
Check out the July 2025 Power BI update to learn about new features.
User | Count |
---|---|
25 | |
10 | |
7 | |
6 | |
6 |
User | Count |
---|---|
30 | |
11 | |
11 | |
10 | |
6 |