Multiple filters on multiple tables

[Anonymous]

I want to have 2 filters on my 3 tables.

The 3 tables,

  

I want to filter date and name independently and have something like this (in those screenshots there are no relationships):

In this example the calculated table is not right, i want it to be only one 'day' per row and either blank or the corresponding value for the other fields.

I really don't know how to go about this, as I can't link two 'parent' tables with an active relationship to the 3 tables and I tried through power query to merge everything in one table but got some trouble, and I am not an expert on any of this. 

Do you have any guidance on what would be the best way to go about this ?

Thank you.

[PaulDBrown · Accepted solution]

@Anonymous 

1) Create dimension tables in Power Query or using DAX for both the name and date fields. You then create a one-to-many relationship with the corresponding fields in the fact tables:

 2) create SUM measures for the values in each table

3) create the slicers using the fields from the dimension tables. Create the visuals using the fields from the dimension tables and add the measures

I've attached the sample PBIX file for you

[Anonymous]

Thank you, it worked like a charm even on my original 8 tables. (i know i said 3 but its easier to ask)

Also to mention it also works on averages and/or percentages

[PaulDBrown]

@Anonymous 

Glad to hear!

it will work with any calculation. This is the recommended model structure for multiple fact tables containing common fields. The use of the dimension tables allows for the filter context, where equivalent rows (fields) are filtered by the common dimension to allow for calculations. 
Let us know if you need any further help!

Best,

Paul

[Anonymous]

Create the separate table for Name and date and then relate those table with this tables. 
and for values create the measure. 
I think this should work