Relationships, Multiple Tables, and Dax Calculations

[DFPRO]

I have a data set with the following tables: 

Dimension: 

- Roster - name of all referees across multiple leagues

Facts: 

- Match Events - a line for each "Match Event" including referee name, what the event was (goal, free kick, etc.)

- Assignments - a line for each match with the names of referees assigned and their various positions (center referee, assistant #1, etc.)

I have made a dashboard that compares referees across various metrics, such as Age or Years as a Referee, which is pulled from the Roster dim table. I would like to now be able to filter by "Game Count," or the number of games they have worked in the year. However, I would also like to be able to filter this by league. 

Example: 

Tom has refereed 10 games in 2023, 5 in the British League and 5 in the French League. If I have a filter or slicer for "League" selected, I would like it to know that Tom has worked 5 in the British League, as opposed to 10 overall. 

I have created some additional tables that calculate (via calculated columns) and display game counts across all of these leagues, which works. However, in order to slice by league-specific game count, I have to create an additional dim table for each league and an additional slicer for each league. 

Along with this, I cannot determine how to connect the Game Count table I created to my Roster table in a way that allows me to filter by all metrics at once. It is either Game Count or everything else, but not both. 

This may not be enough to go on for assistance, so I can update with more info if needed. 

[123abc · Accepted solution]

It sounds like you're trying to build a Power BI report that allows you to filter referees' game counts by league, while also comparing referees across various metrics. To achieve this, you can use a combination of relationships, calculated columns, and measures. Here's a step-by-step approach:

1. Data Modeling and Relationships:

   Ensure that you have proper relationships between your tables:

   • Roster table should have a unique identifier (e.g., Referee ID).
   • Assignments table should have a relationship with the Referee ID in the Roster table. This relationship allows you to connect referees to their assignments.

   It seems like you already have tables that calculate game counts for each referee across different leagues. If not, you can create them.

2. Creating a Measure for Game Count by League:

   Create a DAX measure that calculates the game count by league. This measure should consider the selected league from a slicer. You can use the SUMX and FILTER functions to achieve this. Here's a simplified example:

Game Count by League =
VAR SelectedLeague = SELECTEDVALUE('LeagueSlicer'[LeagueName])
RETURN
SUMX(
FILTER('Assignments', 'Assignments'[League] = SelectedLeague),
1
)

1. In this example, 'LeagueSlicer'[LeagueName] should be the name of the slicer that allows you to select the league.

2. Create Metrics Measures:

   For your various metrics (e.g., Age, Years as Referee), create separate DAX measures in the Roster table.

3. Build Your Visuals:

   Create visualizations on your report canvas that show the metrics you want to compare referees by (e.g., Age, Years as Referee). Use the measures created in step 3 for these metrics.

4. Add Slicer for League:

   Add a slicer to your report canvas that allows you to select the league (e.g., British League, French League). This slicer will be based on the 'League' column in your Assignments table.

5. Create a Measure for Filtered Metrics:

   To calculate metrics based on the selected league, create a DAX measure that uses the FILTER function to apply the slicer's selection to your metrics measures. For example:

Filtered Age =
CALCULATE(
[Age], // Replace with your actual Age measure
FILTER('Assignments', 'Assignments'[League] = SELECTEDVALUE('LeagueSlicer'[LeagueName]))
)

1. Create similar measures for other metrics you want to filter by league.

2. Build Visuals for Filtered Metrics:

   Create additional visualizations that use the measures created in step 6 for metrics that you want to filter by league.

With these steps, you should be able to compare referees across various metrics and filter the game count by league using slicers. The key is to create measures that consider the selected league and then build visuals based on those measures. You can apply this approach to other metrics as needed.

[DFPRO]

Thank you. I believe this works for the first level of my issue. 

Going further with this, I have another set of tables for 2022's data, instead of 2023. I have not found a way to relate the Game Count per Referee in 2022 to 2023 that also allows me to slice by various metrics in the KMI section.