Date filter / Slicer with Multiple tables

[The8]

In have a tables
Table 1 in which I have columns Material A (Unique values), Density, Customer
Table 2 in which I have colmns Material B, Date, Cost B (material B is with multiple duplicates values)
Table 3 in Which I have Column Material C, Date, Cost C (material C is with multiple duplicates values)

-I have relationship between Material A and Material B (One to many relationship and Cross filter direction Single)
-I have relationship between Material A and Material C (One to many relationship and Cross filter direction Single)

-I have created a table visualisation in which I have Material A, Density, Customer, Cost B, Cost C

Now I would like to have a date filter and when I select particular month in Date filter then I should have those cost B and Cost C values in which I would like to see Material A, Density, Customer, Cost B, Cost C.

How can I do it ?

Note: I tried to create a date table and create relationship between date table and Table 2, Table 3 but when I selected particular month then I can only see Cost b, Cost C column in table and all other column values in table are empty but I would like to see all table 1 values as well.

[Elena_Kalina · Accepted solution]

Hi @The8 

Try to do the following

Create a Master Date Table (if you haven't already)

DateTable = 
CALENDAR(
    MINX(UNION(VALUES(Table2[Date]), VALUES(Table3[Date])), [Date]),
    MAXX(UNION(VALUES(Table2[Date]), VALUES(Table3[Date])), [Date])
)

Add columns for Year, Month, etc. as needed.

Set Up Relationships:

• DateTable[Date] → Table2[Date] (Many-to-One, Both Directions)

• DateTable[Date] → Table3[Date] (Many-to-One, Both Directions)

• Keep your existing relationships between Table1 and Table2/Table3

  Create Measures for Cost Calculations

  Total Cost B = SUM(Table2[Cost B])

  Total Cost C = SUM(Table3[Cost C])

  Modify Your Table Visualization:

  • Columns: Material A, Density, Customer

  • Values: Total Cost B, Total Cost C

If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it! 

Thank you.

[DataNinja777 · Accepted solution]

Hi @The8 ,

To achieve the desired outcome where a date slicer filters costs from Table 2 and Table 3 while still displaying all corresponding information from Table 1, the recommended and most robust method involves creating a dedicated Date Table and using DAX measures. The issue you encountered, where columns from Table 1 become blank, occurs because the filter context from your date slicer flows to Table 2 and Table 3, but the single cross-filter direction prevents that filter from traveling "upstream" to Table 1. To fix this, first, you should create a proper Date table using a DAX expression like the following, which will serve as the single source for all date-based filtering.

Date =
CALENDAR (
    MIN ( MIN ( Table2[Date] ), MIN ( Table3[Date] ) ),
    MAX ( MAX ( Table2[Date] ), MAX ( Table3[Date] ) )
)

After creating this table, establish one-to-many relationships from this new Date table to both Table 2 and Table 3 using their respective date columns. Your existing one-to-many relationships from Table 1 to Table 2 and Table 3 should remain as they are. The most critical step is to then create explicit DAX measures for your costs instead of using the raw Cost B and Cost C columns in your visual. You can create these measures using simple aggregation functions. For Cost B, you would use:

Total Cost B = SUM(Table2[Cost B])

And similarly, for Cost C, you would create this measure:

Total Cost C = SUM(Table3[Cost C])

Now, construct your table visualization by dragging Material A, Density, and Customer from Table 1, and use the newly created [Total Cost B] and [Total Cost C] measures. Create your date slicer using a column from your new Date table (e.g., Month). When you select a month, the filter context is applied correctly to the measures, which calculate the costs for the selected period for each material. The details from Table 1 will remain visible because the table itself isn't being filtered, only the calculations are. An alternative, though less recommended, approach is to change the cross-filter direction on the relationships between Table 1 and your other tables to "Both". This would force filters to travel "upstream," but it can create ambiguity and performance issues in more complex data models, making the Date Table and measure approach the superior best practice.

Best regards,

[mark_endicott · Accepted solution]

@The8 - Unless I misunderstand your model this can be created easily and you shouldnt lose any values from Table 1 by connecting a date table to table 2 and 3. 

I've attached a sample file based on the description of your data to show how this can be done. 

If I answered your question please mark my post as the solution, it helps others with the same challenge find the answer!

[DataNinja777 · Accepted solution]

Hi @The8 ,

To achieve the desired outcome where a date slicer filters costs from Table 2 and Table 3 while still displaying all corresponding information from Table 1, the recommended and most robust method involves creating a dedicated Date Table and using DAX measures. The issue you encountered, where columns from Table 1 become blank, occurs because the filter context from your date slicer flows to Table 2 and Table 3, but the single cross-filter direction prevents that filter from traveling "upstream" to Table 1. To fix this, first, you should create a proper Date table using a DAX expression like the following, which will serve as the single source for all date-based filtering.

Date =
CALENDAR (
    MIN ( MIN ( Table2[Date] ), MIN ( Table3[Date] ) ),
    MAX ( MAX ( Table2[Date] ), MAX ( Table3[Date] ) )
)

After creating this table, establish one-to-many relationships from this new Date table to both Table 2 and Table 3 using their respective date columns. Your existing one-to-many relationships from Table 1 to Table 2 and Table 3 should remain as they are. The most critical step is to then create explicit DAX measures for your costs instead of using the raw Cost B and Cost C columns in your visual. You can create these measures using simple aggregation functions. For Cost B, you would use:

Total Cost B = SUM(Table2[Cost B])

And similarly, for Cost C, you would create this measure:

Total Cost C = SUM(Table3[Cost C])

Now, construct your table visualization by dragging Material A, Density, and Customer from Table 1, and use the newly created [Total Cost B] and [Total Cost C] measures. Create your date slicer using a column from your new Date table (e.g., Month). When you select a month, the filter context is applied correctly to the measures, which calculate the costs for the selected period for each material. The details from Table 1 will remain visible because the table itself isn't being filtered, only the calculations are. An alternative, though less recommended, approach is to change the cross-filter direction on the relationships between Table 1 and your other tables to "Both". This would force filters to travel "upstream," but it can create ambiguity and performance issues in more complex data models, making the Date Table and measure approach the superior best practice.

Best regards,

[Elena_Kalina · Accepted solution]

Hi @The8 

Try to do the following

Create a Master Date Table (if you haven't already)

DateTable = 
CALENDAR(
    MINX(UNION(VALUES(Table2[Date]), VALUES(Table3[Date])), [Date]),
    MAXX(UNION(VALUES(Table2[Date]), VALUES(Table3[Date])), [Date])
)

Add columns for Year, Month, etc. as needed.

Set Up Relationships:

• DateTable[Date] → Table2[Date] (Many-to-One, Both Directions)

• DateTable[Date] → Table3[Date] (Many-to-One, Both Directions)

• Keep your existing relationships between Table1 and Table2/Table3

  Create Measures for Cost Calculations

  Total Cost B = SUM(Table2[Cost B])

  Total Cost C = SUM(Table3[Cost C])

  Modify Your Table Visualization:

  • Columns: Material A, Density, Customer

  • Values: Total Cost B, Total Cost C

If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it! 

Thank you.