dynamic table creation

[MichaelPercival]

Hello, Thanks for any advice,

I'm very new to PowerBI, and am trying to create a rota system. Basically I want to be able to have users select the date range, THEN have powerbi to decide which information to colect from various tables to populate each day. In other words, I don't want to have to create a table that calculates and holds all the collected information for each person for the whole year, but rather just give a DAX expression (similar to the excel formula below, which checks each displayed day for what should be displayed based on 'date from'/ 'date to' fields in source tables. I'm thinking that would be fast enough for the usual user just checking their next week ahead, but is flexible enough for a manager to check their whole team's holiday from two years ago in case of discrepencies.

I'd appreciate I'm perhaps too ignorant of Powerbi to helpfully express the problem, but I'd appreciate at least knowing if it sounds possible, and if so any hints for where to look/what terms might be useful to me?

(I'm looking for some way of checking the selected dates, and calling the shift that the person was\will be working for that day, plus any other useful information for the day (holiday/availibility, etc.) 

Currently have all this in Excel, but for reporting we have to keep a whole year's worth of formulas populated in a table, and it's at the limits of memory usage\can't be extended to the rest of the company\is recreated for each new year.

[Anonymous · Accepted solution]

Hi @MichaelPercival ,

I think your need is to create a dynamic scheduling system that can then be dynamically selected based on dates, rather than creating a table of all personnel data.

We begin by creating a date table with which to filter.

Date table = CALENDAR(DATE(2024,6,1),DATE(2024,7,31))

And then we create a slicer to filter on dates to dynamically select the status of each employee.

ShiftOnSelectedDate = 
VAR selectedDate = MAX('Date table'[Date])
RETURN
CALCULATE(
   FIRSTNONBLANK('shifts_large 1'[Shift], 1),
   FILTER('shifts_large 1', 'shifts_large 1'[StartDate] <= selectedDate && 'shifts_large 1'[EndDate]>= selectedDate)
)

AvailabilityOnSelectedDate = 
VAR selectedDate = MAX('Date table'[Date])
RETURN
CALCULATE(
   FIRSTNONBLANK('availability_large'[Availability], 1),
   FILTER('availability_large', 'availability_large'[StartDate] <= selectedDate && 'availability_large'[EndDate] >= selectedDate)
)

HolidayOnSelectedDate = 
VAR selectedDate = MAX('Date table'[Date])
RETURN
CALCULATE(
   FIRSTNONBLANK('holidays_large (1)'[HolidayType], 1),
   FILTER('holidays_large (1)', 'holidays_large (1)'[StartDate] <= selectedDate && 'holidays_large (1)'[EndDate] >= selectedDate)
)

If you still have questions about this, check out my attachment, I hope it helps.

Hope it helps!

Best regards,
Community Support Team_ Tom Shen

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

[Anonymous · Accepted solution]

Hi @MichaelPercival ,
For this kind of problem, we can the MEASURES I have written to perform a merge into a VALUE value for the operation.

CombinedInfo = 
VAR shift = [ShiftOnSelectedDate]
VAR holiday = [HolidayOnSelectedDate]
VAR availability = [AvailabilityOnSelectedDate]
RETURN
CONCATENATE(
   CONCATENATE(shift, IF(shift <> "" && holiday <> "", "; ", "")),
   CONCATENATE(holiday, IF((shift <> "" || holiday <> "") && availability <> "", "; ", "") & availability)
)

Remove column totals and row totals to make it more relevant to your needs.

In this case, you can also use Power BI's conditional formatting to go ahead and set it up so that the formatting in your Power BI is equivalent to the formatting in your execl.
Here is the documentation I found for you hope it helps.

Apply conditional table formatting in Power BI - Power BI | Microsoft Learn

Hope it helps!

Best regards,
Community Support Team_ Tom Shen

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

[MichaelPercival]

Amazing, this is exactly what I needed. Can I just ask to see your pbix file for the above - I'm wondering what 'relationships', if any, you've got between your date table and 'employee' table, as I think this is where I'm failing.

[Anonymous]

Hi @MichaelPercival ,

I am very honoured that I can help you with your needs, here is the relationship between my table and the table, I hope it will help you to solve your problem.

Hope it helps!

Best regards,
Community Support Team_ Tom Shen

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

[MichaelPercival]

This has got me a massive step forward! I'm just struggling to get the matrix to display the information for all dates between the start and end date - it's just showing on the start date. I'm wondering if it's something to do with the 'parameter' you've set up on your version- could you explain what your parameter does? 

Failing that, I'm wondering if it might have something to do with the way my relationships are setup?

[Anonymous · Accepted solution]

Hi @MichaelPercival ,

I think your need is to create a dynamic scheduling system that can then be dynamically selected based on dates, rather than creating a table of all personnel data.

We begin by creating a date table with which to filter.

Date table = CALENDAR(DATE(2024,6,1),DATE(2024,7,31))

And then we create a slicer to filter on dates to dynamically select the status of each employee.

ShiftOnSelectedDate = 
VAR selectedDate = MAX('Date table'[Date])
RETURN
CALCULATE(
   FIRSTNONBLANK('shifts_large 1'[Shift], 1),
   FILTER('shifts_large 1', 'shifts_large 1'[StartDate] <= selectedDate && 'shifts_large 1'[EndDate]>= selectedDate)
)

AvailabilityOnSelectedDate = 
VAR selectedDate = MAX('Date table'[Date])
RETURN
CALCULATE(
   FIRSTNONBLANK('availability_large'[Availability], 1),
   FILTER('availability_large', 'availability_large'[StartDate] <= selectedDate && 'availability_large'[EndDate] >= selectedDate)
)

HolidayOnSelectedDate = 
VAR selectedDate = MAX('Date table'[Date])
RETURN
CALCULATE(
   FIRSTNONBLANK('holidays_large (1)'[HolidayType], 1),
   FILTER('holidays_large (1)', 'holidays_large (1)'[StartDate] <= selectedDate && 'holidays_large (1)'[EndDate] >= selectedDate)
)

If you still have questions about this, check out my attachment, I hope it helps.

Hope it helps!

Best regards,
Community Support Team_ Tom Shen

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

[MichaelPercival]

Thanks again for this. I never did manage to work out why mine was only showing events on the 'start date', but created a work around by updating the flow to create new a row for each day that an even applied to

[MichaelPercival]

Thanks Tom,

This looks like it gets me some of the way, but I need the visual format to remain as in our old excel system (The multi-coloured calendar screenshot above), with the filtered dates showing along the top - the x axis , and the names along the left - the y axis. Then I need all the relevant information concatenated from different tables into the 'cells'.

In other words, I want each row to show the name, each 'column header' to show the (filtered) dates, and then the 'cells' under each date to concatenate all the relevant information that applys to that person on the selected date.

Thanks again for your input. I'm sure this gets me half way there. It's working out how to use and refer the formula to the selected dates on the x axis that I still can't work out...

Michael