Row per person per day

[FaithLivi0]

I am developing a report that pulls data from a live database, and the report is for the purposes of looking at service users over the last few years up to the present. We are pulling data on things like their residential placements, legal statuses and how long they've been involved with our organisation - all these things either have a start and end date, or a start date and are currently ongoing. 

Currently we have multiple tables which have 1 row per service user per day they were supported by the service, or 1 row per resident per day they were a resident. So if a service user was supported from 1st – 31st March 2022, there will be 31 rows with that service user’s details, and if they were a resident from  3rd – 12th March 2022 there will be 10 rows with that resident’s details. We want to be able to see information about the residents or service users who were in residence or using the service between certain dates or on a set date – is it possible to do this without having 1 row per person per day? As the dataset grows, having multiple rows per person will become increasingly unmanageable in terms of loading and refresh times.

Additionally, there may be overlapping periods between the different tables. So for example, a service user may have a legal status that starts on 1st March 2022 and ends 31st May 2022, but they have 2 placements within that time – one from 1st February – 31st March 2022 and a second from 1st April to 4th June 2022. We want to be able to select a date (or date range) and see all active periods (or service use, residential care, legal status etc.) at/during this time.

Does anyone have a suggestion as to how we can avoid having 1 row per person per day, and just have 1 row per placement or 1 row per legal status, whilst also allowing Power BI to recognise whether a placement or legal status for a person was active or inactive on or during a set time? 

Thanks!

[Greg_Deckler]

@FaithLivi0 Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.