Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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%:
Solved! Go to Solution.
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.
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.
I should maybe add that when I use
@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%)
User | Count |
---|---|
42 | |
28 | |
23 | |
18 | |
15 |
User | Count |
---|---|
54 | |
35 | |
18 | |
17 | |
15 |