Forum Discussion
Helper IVCalculate available time from resource table, resource_availability dim and bookings fact tables
Community ChampionCertainly, I understand the issue now. It seems that the relationship between the Date table and the Bookings table is causing the total available hours to be aggregated over the entire week rather than being specific to the selected date. To address this, you can create a new relationship between the Date table and the Bookings table based on the weekday.
Here are the steps you can take:
Create a Weekday column in the Date Table: If you don't have a weekday column in your Date table, create one. You can use the following DAX formula:
Measure:
Weekday = WEEKDAY(DateTable[Date], 2)
This formula assumes that your weekdays start from Monday (ISO week numbering).
Create a Relationship between Date Table and Bookings Table: Create a new relationship between the Date table and the Bookings table using the Weekday column in both tables.
Modify the TotalMinutesAvailable Measure: Adjust the TotalMinutesAvailable measure to consider the new relationship and calculate the available minutes for each selected date.
Measure:
TotalMinutesAvailable =
RELATED(Resource_availability[WorkingMinutesPerDay]) -
CALCULATE(
IF(
Bookings[Category] = "Unavailable",
RELATED(Resource_availability[WorkingMinutesPerDay]),
0
),
USERELATIONSHIP(Bookings[Date], Date[Date])
)
In this formula, the USERELATIONSHIP function is used to specify the relationship between the Date column in the Bookings table and the Date column in the Date table based on the Weekday.
Now, when you select a specific date, the TotalMinutesAvailable measure should consider only the related weekday and provide the correct available minutes for that date.
Remember to adjust column and table names based on your actual model. If you encounter any issues or need further clarification, feel free to ask!
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.
In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.
Helper IVThank you very much for your replay. I do have couple of questions.
First One is that Create a Relationship between Date Table and Bookings Table: This relation is many to many with week numbers between Date table and bookings table right?
Second I am getting the following error while trying to write the measure. Could you please let me know what is the reason behind it.
- 123abc
can you share data sample (pbix file) file please
- 123abc
Let's address your questions one by one:
1. Relationship Between Date Table and Bookings Table:
The relationship between the Date table and the Bookings table typically isn't a many-to-many relationship based on week numbers. Instead, it's typically a one-to-many relationship where each date in the Date table can have multiple bookings in the Bookings table.
However, if you want to consider week numbers for some calculations, you can create an additional column in both tables that represents the week number, and then establish a relationship based on that. But for the context of your previous questions, a direct relationship between the Date table and the Bookings table based on the date column should suffice.
2. Error Message:
You mentioned that you're encountering an error while trying to write the measure. To provide guidance on resolving the error, I'd need to see the specific error message you're receiving.
Please provide the exact error message or describe the issue you're facing in more detail. This will help me understand the problem better and offer a solution or workaround accordingly.