Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
R_S
Helper I
Helper I

How to find number of records based on a value from another table

I have records in table A (BaseInfo) that contains data on an individual, specificly their region. I then have a second table which has responses from a particular multiple choice question they may or may not have answered.  For instance, suppose I have 10 records for respondents from "Europe" in table A, and of those 10, 8 answered something for a question ("Q6") but only 4 chose "C" as an answer.  I want to show that 50% of the respondents from Europe who answered something for Q6 answered 'C' (4 out of 8. )

 

For instance this will tell me the % out of the over all respondents from the region who answered the question, i.e. 40% using the above example, not 50%: 

% Q6 Responded = DIVIDE(COUNT(Q6[ResponseId]), COUNT(BaseInfo[ResponseId]), 0)
 
How can I modify this so only those who answered actualy answered something for Q6 gets counted in the measure? 
1 ACCEPTED SOLUTION
R_S
Helper I
Helper I

After much experimenting, I decided to create a region column in the child table and use that. Then this works, although I am not quite sure why and perhaps there is a more elegant solution.

 
CALCULATE(DISTINCTCOUNT(Q6[ResponseId]), ALLEXCEPT(Q6, Q6[Region], Q6[Response])) / CALCULATE(DISTINCTCOUNT(Q6[ResponseId]), ALLEXCEPT(Q6, Q6[Region]))

View solution in original post

4 REPLIES 4
R_S
Helper I
Helper I

After much experimenting, I decided to create a region column in the child table and use that. Then this works, although I am not quite sure why and perhaps there is a more elegant solution.

 
CALCULATE(DISTINCTCOUNT(Q6[ResponseId]), ALLEXCEPT(Q6, Q6[Region], Q6[Response])) / CALCULATE(DISTINCTCOUNT(Q6[ResponseId]), ALLEXCEPT(Q6, Q6[Region]))
R_S
Helper I
Helper I

I should maybe add that when I use 

 

DIVIDE(COUNT(Q6[ResponseId]), CALCULATE(DISTINCTCOUNT(Q6[ResponseId]), ALL(Q6)), 0)
 
in a bar chart with the response (A, B, C, etc) as the axis with no legend it returns the correct response, but when I add the Region from BaseInfo table as a legend then it doesn't give the correct response anymore
amitchandak
Super User
Super User

@R_S , Is table A is joined with Table B directly. 

Or they should be joined by a dimension/bridge table.

https://www.seerinteractive.com/blog/join-many-many-power-bi/

 

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

 

 

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

@amitchandak Yes, there is a one to many relationship between the two tables.   I can't share the pbix due to the data unfortunately. 

 

The only important columns would be:

Base Info table:  ResponseId, Region

Q6: ResponseId, Response

 

E.g:

Base Info:

1, APAC

2, Europe

3, APAC

4, APAC

etc

 

Q6:

1, A

1, B

2, A

2, B

2, C

2, D

3, A

3, C

 

etc

 

Here we see only 1 and 3 answered Q6 from APAC (4 did not) and while both answered A (100%) only 1 answered B (50%) or C (50%)

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.