Multiple Date Columns and USERELATIONSHIP

[Noah]

Hello, everyone!

I am currently helping out my spouse with a Power BI dashboard for her work. She has a table that she cannot modify the columns of due to rules restrictions.

I've made and shared a copy of the important columns from the Excel giving me issues here.

I also made a simplified copy of the Power BI file.

It goes like this:

1. Client: I use it for some charts, but none that are causing my issues. I included it since it's basically an ID.
2. ID: Same as above.
3. Worker: Used in some charts for filtering purposes when trying to see data from the different workers involved.
4. Date Received: Not the primary date column for metrics, BUT used for any status that is not "Completed".
5. Type: The different services/tasks done by the workers. This is what she needs to count depending on which date applies. You could say it's the primary column for keeping track of each task. It should be filtered by the selected Status, Worker, and always have the most up-to-date information available, with the following date columns in priority order: Date of Cancellation, Date Completed, Date of Service, or Date Received.
6. Date of Service: This always happens ON or AFTER Date Received. Basically, it's the date in which the Client was offered the service Type.
7. Date Completed: This is when the service Type is finished. This is the date used for keeping track of KPIs for the fiscal year and is also THE primary date range used in the Power BI dashboard.
8. Date of Cancellation: Whenever a service Type is cancelled, this date is entered. It's impossible to have a Date Completed AND a Date of Cancellation. This Date is important when counting the number of Cancelled found in the Status column.
9. Status: The current status of each service Type. This is used in one of the column charts to filter different things on different charts.

My issue is that, for example, if a Type's Status is currently "In Progress", then it has no Date Completed, even though that is the primary Date column we use for the dashboard. This happens pretty much to any Type that has no Date Completed. Therefore, if in the date filter I have a date range that doesn't include any other dates other than Date Completed, the Status isn't displayed.

I've read that USERELATIONSHIP might help me with this issue in a basic way, but I am not sure how to use it or if it's the best way to do it.

How can I make it so that I can use all of the associated dates when necessary while keeping Date Completed as the primary one?

[v-csrikanth]

Hi @Noah 

We haven't heard from you since last response and just wanted to check whether the solution provided has worked for you. If yes, please Accept as Solution to help others benefit in the community.
Thank you.

If the above information is helpful, please give us Kudos and mark the response as Accepted as solution.
Best Regards,
Community Support Team _ C Srikanth.

[v-csrikanth]

Hi @Noah 

I wanted to follow up since I haven't heard from you in a while. Have you had a chance to try the suggested solutions?
If your issue is resolved, please consider marking the post as solved. However, if you're still facing challenges, feel free to share the details, and we'll be happy to assist you further.
Looking forward to your response!

Best Regards,
Community Support Team _ C Srikanth.

[v-csrikanth]

Hi @Noah 

Thank you for being part of the Microsoft Fabric Community.

As highlighted by @Greg_Deckler , the proposed approach appears to effectively address your requirements. Could you please confirm if your issue has been resolved?
If you are still facing any challenges, kindly provide further details, and we will be happy to assist you.

Best Regards,
Cheri Srikanth.

[Greg_Deckler · Accepted solution]

@Noah I would probably go with a disconnected (unrelated) date table and then form the relationship in a measure checking first for Date completed but if BLANK, check a different date.

You might be able to use USERELATIONSHIP. You would need to form relationships between your date table and all the date columns in the table with the active relationship being Date Completed. Would be a simplar process of checking of Date Completed is blank and then using CALCULATE with USERELATIONSHIP. 

IMHO, the first approach is going to be easier to do and debug.

[Noah]

Hello, @Greg_Deckler 

I have that considered as my end of the line solution. I want to avoid trying to use up more real estate doing just that if I can, seeing as it's been an issue I have faced before with other projects and I regret not coming up with a cleaner, more elegant solution.

If push comes to shove, well, guess I'll do just that! Thanks for your response though.