Problems with Date filtering

[ChrisAZ]

So I am trying to create this Dashboard that shows two sets of hours. One comes from a data source that shows how much is being charged on work orders and the other is the amount of hours paid by accounting.  I am trying to include a screenshot, but cannot see an option anywhere in the post creation to attach files. Basically, when I use the Filter on the right hand side and select a month, it is only updating the data for one of these hours columns and not the other. I cannot figure out how to get them to sync. If someone can advise on how to add screenshots, I would be happy to add that in as well to provide better context.

[ajohnso2]

Hey Chris, 

It would be beneficial if you could share a copy of your model with any sensitive data masked/randomised (You can upload the pbix to something like onedrive and share the link here).

The issue is likely to do with the table relationships defined in your model.

[ChrisAZ]

Does the following link work for this? The Dashboard that I am looking for help on is the one on labor. Thanks for any suggestions.

https://drive.google.com/file/d/19mpW2gCHJ01jhO0NuKS38d_5MdP7oIuZ/view?usp=sharing

[ajohnso2]

Hey Chris looks like Daniel has mentioned this issues below however take a look at the attached which should give you an idea of where you need to be, signing off for the day but around to answer questions tomorrow.

Dashboard.pbix

[ChrisAZ]

Thanks! I will download this and take a look. Much appreciated!

[DanielW_ · Accepted solution]

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.

[ChrisAZ]

Starting to work on this and getting an error when trying to create this Date column. Can you advise on what I am missing?

[DanielW_]

Apologies, for the DATE function you should use only numeric values, so should use an numeric alternative for the month. You could use: SWITCH('Shop Labor - Fleetio'[Month],

"October", 10,

"November", 11

)

and possible extended for the rest of the months, or if you can have a full date column in this table from the source even easier. 

[ChrisAZ]

Thanks. So would that be the calculation to do instead of the other?

[DanielW_]

No, you can use it as a variable in your date formula, like this:

Date =

//Create the numeric value for the month based on the name

VAR MonthNumber =

    SWITCH(

        'Shop Labor-Fleetio'[Month],

        "October", 10,

        "November", 11

    )

RETURN

//Create a date within the month, set to the 1st here. Doesnt matter when filtering based on year and month

    DATE('Shop Labor-Fleetio'[Year], MonthNumber,1)

[ChrisAZ]

So this appears to be functioning correctly now. Can you give it a quick look over to see if I instituted those changes how you described for efficiency?  This has been really helpful!

https://drive.google.com/file/d/19mpW2gCHJ01jhO0NuKS38d_5MdP7oIuZ/view?usp=drive_link

[DanielW_]

The only thing you are missing is the relationship between between DateTable[Date] and Shop Labor - Accounting[Date].
Since there is already a date column in the table you can use that, no need to create a new one like in the Fleetio table.
Then you'll end up with a model like this:

[ChrisAZ]

Thanks so much for taking the time to do this. I definitely had a sense my relationship setup was not ideal, but was kind of trying out different options to see what I could get to work. I am going to try and go through your steps provided and see if I can get that to work. Will keep you posted, and again, thanks so much for your time in explaining this!

Chris

[ChrisAZ]

Any suggestions how to quickly randomize/mask data without having to modify the source link data? Sorry new to this and not super efficient at it. Thanks!

[Anonymous]

Hi @ChrisAZ ,

You can share several rows of data that are responsive to the problem and show the expected results you want, which will solve the problem more effectively.

Best regards,
Community Support Team_ Scott Chang

[DanielW_]

Can you share a screenshot of your datamodel as well and if possible some (anonimized) sample data. 
Is the column 'Date - Month' in your filter from the independent date table? In that case can you check if it filters the values when you add only one of the columns? Otherwise it would point to an issue in the relationship.

[DanielW_]

Hi Chris,
you paste the screenshots in the text box or use the 'Insert Photos' button and upload the file(s). 

For your question, did you create a relationship between the tables from the two sources? Otherwise PowerBI wan't filter the other column by a field from the other table. It will be the easiest for members to answer your question if you provide some sample data.

[ChrisAZ]

Hi Daniel,

I am not sure how I did not see that option before. Thanks! I have tried creating relationships both directly and with an independent Date table but I still cannot get that Word Order Hours column to change values. What is interesting is that if I select one month (I only have data loaded for October and November) it will change the list of names for those months if there are some differences, but for most of the technicians that are in both months, it does not adjust their hours. It also will not show the total of their hours when no filter is being applied, yet the far right column will.