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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
R_S
Frequent Visitor

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
Frequent Visitor

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
Frequent Visitor

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
Frequent Visitor

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.

 

 

@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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors