Suppose that I have the following four tables of data in Power BI:
Table 1.
| Name | Country | Age | Points |
| Karl | Germany | 14 | 5 |
| Elena | Germany | 15 | 5 |
| Anne | Germany | 13 | 10 |
| Kalle | Sweden | 13 | 15 |
| Pelle | Sweden | 12 | 20 |
| Gloria | Spain | 11 | 10 |
Table 2.
| Code | Hobby | Hobby type |
| 1 | Football | Sport |
2 | Basketball | Sport |
| 3 | Piano | Music |
| 4 | Violin | Music |
Table 3.
| Name | Hobby of interest (code) |
| Karl | 1 |
| Elena | 1 |
| Anne | 1 |
| Kalle | 2 |
| Pelle | 4 |
| Gloria | 3 |
Table 4.
| Name | Hobby (code) |
| Karl | 1 |
| Anne | 1 |
| Gloria | 3 |
- So in tables 3 and 4, the hobbies and hobbies of interest are coded with numbers 1, 2, 3 and 4.
- Some of the people are only interested (table 3) in a specific hobby, some both are interested and practice (table 4) that hobby.
I want to build a table visualisation based on this data.
- I want to consider only German people (names from Table 1, where Country = Germany).
- I want to have two slicers on the same page of my report: one for hobby type (sport/music) and one for hobby (football/basketball/violin/piano; not hobby of interest).
- Then the table shows the average age and average points for those German people who have the given hobby and/or their hobby is in the given hobby type.
For example, if I chose hobby type = sport, then the averages would be calculated for German people, who have a hobby of type Sport, that is, football or basketball. Thus, the averages are calculated for Karl and Anne. The resulting table visualisation would be:
| Average age | Average points |
| 13.5 | 7.5 |
But how could I do this table visualisation? There is a connection between people names in different tables, and connection between hobbies and their codes. I need to be sure that the resulting table is calculated only for people with a given hobby (and/or hobby type), not for every person interested in a given hobby.
