Need help with aggregating a provider appointment schedule data

[shris1993]

Hi Guys,

I have a dataset of providers appointment schedule which looks something like the below table

I have the provider name column , then start date time and end date time for the appointment holds.
I have extracted the time from start date and end date column in two other columns which are start time and end time.
Then I have the reason type.

For my analytical reporting , my team wants to see the data in the below form, 
the table will have a date column (which i will get it from a date dimension table)
the date column will include dates from 2022 start till now and going forward , for each date they want to see how much time was blocked for each provider in a day.

The problem i am facing is in my fact table , I have start date and end date for the appointment , so if the same hold(appointment) repeats for 1 year starting (for example someone blocked 10 am to 11 am every day of the working days, starting from 01-01-2023 and it ends on 05-05-2024) from 2023 and going till 2024 , how do i connect that to the my date dimension table.

Can you please help me with the query.
I want the appointment in the following format.

Date	Provider name	aapointment hour	type
1/1/2024	gongalez	3	out of office
1/2/2024	gongalez	3	out of office
1/3/2024	gongalez	3	out of office
1/4/2024	gongalez	3	out of office
1/5/2024	gongalez	2	initial councelling
1/6/2024	gongalez	2	initial councelling
1/7/2024	gongalez	2	initial councelling
1/8/2024	gongalez	2	initial councelling
1/9/2024	gongalez	2	initial councelling

[v-zhengdxu-msft · Accepted solution]

Hi @shris1993 

Please try this:

First of all, I create a set of sample:

Then add a new table:

Table 2 =
FILTER (
    CROSSJOIN ( 'Data', 'Table' ),
    'Table'[Date] >= 'Data'[Start]
        && 'Table'[Date] <= 'Data'[End]
)

The result is as follow:

If the above one can't help you, could you please provide more raw data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples? It would be helpful to find out the solution. You can refer the following links to share the required info:

How to provide sample data in the Power BI Forum

How to Get Your Question Answered Quickly

And It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Musadev]

The format of the columns can be changed in the table view. add a single measure to the table to count the rows.
add a matrix visual to your report. add all the columns from the table and group it by the count, so you will get the desired results.
TotalCount = COUNTROWS(table_name[Providers])