Modeling sites and incidents

[Anonymous]

I think this is a modeling question but perhaps it's a dax question.

I have two fact tables:

• a list of incidents with site id where they happened
• a list of 100 sites including how many employees at each and which of 4 regions they're in (regions have subregions).

The two tables are linked by site id so one site can have many incidents.

Not every site has had an incident.

I'd like to be able to show how many incidents per N employees there are for each region or subregion and to have that respond on a map of the data I have in Power BI.

Question

In relationships, f I set cross filtering to single, then a card with the no. of employees (as sum) never changes regardless of which region I click.

If I set it both then it'll give me the sum of employees at sites where there's been an incident, rather than of all employees in that region.

Am I right to think I need to create different fact tables or is there some trick with DAX that might resolve this?

[lbendlin · Accepted solution]

You only have one fact table - the incidents.  The other table is a dimension table for the sites, with attributes like region and employee count.

Your relationship should be one way from the dimension table to the fact table based on site id.

In your charts you MUST use the site id from the dimension table, not from the fact table.

Please provide sample data in usable format (not as a picture) and show the expected outcome.