Problems with slicers and dimensions

[Kevin086]

Hi I am fairly new to databases and Power BI, after a day of research I still have the following problem:

For the overvieuw I'm using 3 tables, Companies (Consisting of companies), Verzuimmeldingen (Consisting of employees who are sick), and the table Users(consisting of the company users). 

- Companies has an PK ID

- Users has an PK ID and an FK CompanyID

- Verzuimmelding has an PK ID and FK UserID

Other important columns for this case:

Companies: CreationDate, zip code

Verzuimmeldingen: CreationDate, zip code

Now I'm trying to add an slicer to filter on a general date and location for the tables Verzuimmeldingen and Companies.

So for example when you select the year 2020, it should select the employees from "Verzuimmeldingen" with a creationdate in 2020 AND show the companies with an RegistrationDate in 2020.

Another example, when you select a state, it should select the employees of that state based on their zip-code and select the companies based on their zip codes. (I have already added a location table, but it only works for 1 table becouse its linked on zip code of either Verzuimmeldingen or Companies.) 

Problemdefinition: At this moment I need 2 slicers for date and 2 slicers for location to control the data while the slicers have exactly the same variations.

Desired outcome: I want 1 slicer for date and 1 slicer for location to control 2 cards with for example a count on CompanyID's and one with a count on VerzuimmeldingID's.

I could probably fix the problem by adding FK_locationID and FK_dateID to the table users? But i have no clue how to do so. Help will be much appreciated :)!

This is what i have so far:

[v-yuaj-msft]

Hi @Kevin086 ,

But the "Creation Date" column in the "Verzuimmeldingen" table you gave is all 2021 dates. Do you want to display the "Creation Date" in the "Verzuimmeldingen" table, the "Username" in the "Users" table and the "Company name" in the "Companies" table together?

Best Regards,

Yuna

[TomMartens]

Hey @Kevin086 ,

please create a pbix file that contains sample data, but still reflects your data model. Upload the pbix file to onedrive or dropbox and share the link. If you are using Excel to create the sample data share the xlsx as well.

Also describe the expected outcome based on your sample data.

Regards,

Tom

[Kevin086]

Hey @TomMartens , Thank you for your message. Hereby the dropbox link to the sample pbix file as well as the excel file where i made the sample data. The Location table is the full table which im using right now. The date table is implemented manually with: 

Dim_Kalender = CALENDAR(Date(2015,1,1),TODAY())

I have not connected the dimension tables: Date and Location, because, to solve my problem, both tables should connect to Verzuimmeldingen as well as Companies, which I can't figure out how.

https://www.dropbox.com/sh/jhj7h4tn09nt0dw/AADiHQ7iX0IjK1iSe68cNBoDa?dl=0