Problems with Date filtering

Hi Chris,

Thanks for sharing the additional details — that context is much more helpful. It looks like the root of the issue lies in the design of your data model. I’ll walk you through the key problems and offer some steps to resolve them.

1. Many-to-Many Relationships and Circular References Your current model only contains many-to-many relationships, with bidirectional filtering enabled. Power BI does not allow active relationships that would create circular references. As a result, some of your relationships — particularly between the DateTable and the Shop Labor-Fleetio tables — are inactive (as shown by the dotted lines in the relationship view).

Solution

While it’s possible to activate these relationships using measures, it’s often better to redesign the model to avoid the need for many-to-many relationships. Instead, consider restructuring the model to reduce complexity and avoid circular references.

2. Duplicate Relationships Between DateTable and Other Tables It seems you’ve established two relationships between the DateTable and other tables for both Year and Month. In Power BI, only one relationship between two tables can be active at a time. This is why you’re encountering issues.

Solution

Rather than creating separate relationships for Year and Month, create a combined Year-Month column (or better yet, use a full Date column) in your fact table. If you’re filtering only by month, you can use any date within the month as a reference point. Here’s an example of a DAX formula to create a Date field in the Fleetio table:

Your_date_column = DATE(Year, Month, 1) -- This creates the first day of each month

With this column in place, you can create a single active relationship between the DateTable and your fact tables based on this new Date field.

3. Technician Field Issues It’s best to avoid using many-to-many relationships whenever possible. Since you’re using a Technician field across multiple fact tables, you’re likely running into issues with cross-filtering.

Solution

To resolve this, create a Technician Dimension Table with a distinct list of all technicians. You can achieve this with DAX like so:
TechnicianTable = DISTINCT(UNION(VALUES(ShopLabor[Technician]), VALUES(Fleetio[Technician])))

Once you have this table, create one-to-many relationships between the Technician field in this new dimension table and the Technician fields in your Shop Labor and Fleetio tables. Then, when you want to display Technician information in your reports, use the Technician field from this new table rather than the one from the fact tables.

Summary of Steps

1. Eliminate the existing many-to-many relationships.

2. Create a new "Technician" dimension table with unique values for the Technician field.

3. Establish one-to-many relationships from the Technician table to the Shop Labor and Fleetio tables.

4. Add a Date column to the Fleetio table using a DAX formula (e.g., DATE(Year, Month, 1)).

5. Create relationships between the DateTable and your fact tables using the new Date fields.

6. Update the Technician field in your report to use the one from the new Technician table.

By following these steps, you'll reduce complexity, avoid circular references, and ensure that Power BI’s cross-filtering behaves as expected. This approach also adheres to best practices for Power BI data modeling. The model should then look like this:

Let me know if you'd like any clarification on these points or if you'd like help with the DAX formulas or relationships.