DateSpan

[nchamilton2]

Good afternoon,
I have a datespan table that I'm trying incorporate into my model. I have my fact table, Phases, Datespan and Projects.

I'm trying to avoid the many to many relationship so I cannot attach my datespan to my fact directly. The objective is to be able to pull in ProjectTitle, PhaseName and Date into the same visual. How can I bridge the datespan table to the fact table and prevent a many to many? Or is there a better approach than what I've described?

Thanks

FactTable

ID	ProjectID	PhaseId	UserAssignment	PhaseStartDate	PhaseEndDate
1	1	4624	1	1/31/2025	2/23/2025
2	1	4624	2	1/31/2025	2/23/2025
3	1	4624	3	1/31/2025	2/23/2025
4	1	4624	4	1/31/2025	2/23/2025

Projects

ProjectID	ProjectTitle
1	Project1
2	Project2

Phases

PhaseId	EstStartDate	EstEndDate	PhaseName
4264	1/31/2025	2/23/2025	Planning

DateSpan

Phaseid	Date
4624	1/31/2025
4624	2/1/2025
4624	2/2/2025
4624	2/3/2025
4624	2/4/2025
4624	2/5/2025
4624	2/6/2025
4624	2/7/2025
4624	2/8/2025
4624	2/9/2025
4624	2/10/2025
4624	2/11/2025
4624	2/12/2025
4624	2/13/2025
4624	2/14/2025
4624	2/15/2025
4624	2/16/2025
4624	2/17/2025
4624	2/18/2025
4624	2/19/2025
4624	2/20/2025
4624	2/21/2025
4624	2/22/2025
4624	2/23/2025

[v-tsaipranay]

Hi @nchamilton2 ,

We haven’t received an update from you in some time. Could you please let us know if the issue has been resolved?
If you still require support, please let us know, we are happy to assist you.

Thank you.

[v-tsaipranay]

Hi @nchamilton2 ,

I wanted to follow up on our previous suggestions. We would like to hear back from you to ensure we can assist you further.

Thank you.

[v-tsaipranay]

Hi @nchamilton2 ,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.

Thank you.

[v-tsaipranay · Accepted solution]

Hi @nchamilton2 ,

You're correct that directly joining the DateSpan table to the FactTable introduces a many-to-many relationship, which Power BI doesn’t handle as cleanly as other analytics tools. To support your scenario generating all dates between each phase’s estimated start and end, while avoiding many-to-many joins the recommended approach is to reshape the model slightly by introducing Phases as a bridge table.

First create a dynamic DateSpan table in Power Query that expands each phase into one row per date using this code:

let
    Source = Phases,
    ChangedTypes = Table.TransformColumns(Source, {
        {"EstStartDate", each Date.FromText(_, "en-US"), type date},
        {"EstEndDate", each Date.FromText(_, "en-US"), type date}
    }),
    AddDateList = Table.AddColumn(ChangedTypes, "Date", each List.Dates([EstStartDate], Duration.Days([EstEndDate] - [EstStartDate]) + 1, #duration(1,0,0,0))),
    ExpandedDates = Table.ExpandListColumn(AddDateList, "Date"),
    SelectedColumns = Table.SelectColumns(ExpandedDates, {"PhaseId", "Date", "PhaseName"})
in
    SelectedColumns

Adjust the relationships in the model:

• DynamicDateSpan[PhaseId] → Phases[PhaseId] (One-to-many)
• FactTable[PhaseId] → Phases[PhaseId] (Many-to-one)
• FactTable[ProjectID] → Projects[ProjectID] (Many-to-one)

Use Phases as the central bridge. This lets both the fact table and the generated date list connect indirectly, avoiding many-to-many conflicts.

To show ProjectTitle, you can either create a measure like:

ProjectTitle :=
CALCULATE (
    SELECTEDVALUE ( Projects[ProjectTitle] ),
    TREATAS ( VALUES ( DynamicDateSpan[PhaseId] ), FactTable[PhaseId] ),
    FactTable
)

Or build a calculated table if you need a flattened structure.

This setup should meet your needs: dynamically showing each phase across all active dates, with project info, while maintaining a clean and performant model.

hope this helps, please feel free to reach out for further issue.

Thank you.

[v-tsaipranay]

Hi @nchamilton2 ,

Thank you for reaching out to the Microsoft fabric community forum. Thank you @FBergamaschi and @amitchandak  for your inputs on this issue.

After thoroughly reviewing the details you provided, I was able to reproduce the scenario, and it worked on my end. The model should have One -Many relation between Fact Table and DateSpan between Date and PhaseStartDate for it to show the table with Date, Phase Name and Project Title.

Please refer to the following images for your understanding:

output: 

I am also including .pbix file for your better understanding, please have a look into it:

Hope this helps you resolve the issue. Please reach out for further assistance.

If this post helps, then please give us ‘Kudos’ and consider Accept it as a solution to help the other members find it more quickly.

Thank you for using Microsoft Community Forum.

[nchamilton2]

Thanks for the reply. I'm looking for all dates between the EstPhaseStart and EstPhase End. So if phase start was 1/1/2025 and PhaseEnd was 1/15/2025, I would expect to see 
Date          Phase
1/1/2025   Planning

1/2/2025   Planning
1/3/2025   Planning

etc..

It's a slowly changing dimension, I just can't figure out how to incorporate it in Power BI. Other analytics tools, no probablem, but because of the many to many in power bi, it's difficult.

[Ashish_Mathur]

Hi,

Based on the 4 tables that you have shared, show the expected result very clearly.

[FBergamaschi]

I do not see the many to many thing, which columns connected would result in a many to many in your view?

To what I see, connect the calendar to either the start or the end dat of the project and that's it

Or maybe in your DateSpan the dates would repeat as you have mutiple Phaseid?

In that case please consider for the calendar only the colum dates (and months etc), but nothing else

So data model

Connect The date table to Fact

Connect the Phases table to Fact

Connect the Project Table to fact

Connect the calendar (without any ohase column in it) to Fact

If this helped, please consider giving kudos and mark as a solution

@me in replies or I'll lose your thread

consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI

[nchamilton2]

Thanks for the reply. I'm not able to connect the date table to the fact table because it generates a many to many if I connect it via the phaseid. If I connect via the date from Datespan to Phasestartdate in fact I lose the ability to utilize the spanned dates. I want to be able to return a date for each day for each phase between phasestart and phaseenddate.

Thanks.

[FBergamaschi]

The date table needs to be connected to the fact table via the date column (and the pahse needs to be eliminated from the date table, that is a date table), the rest will be done via DAX

[nchamilton2]

Can you please point me in the right direction of what the next steps are in DAX please?

[amitchandak]

@nchamilton2 , If you are try to deal with between range. These solutions can help 

Power BI: HR Analytics - Employees as on Date : https://youtu.be/e6Y-l_JtCq4
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
Power BI HR Active Employee Tenure Bucketing, and Hired, Terminated, and Active employees: https://youtu.be/fvgcx8QLqZU

Measure way
Power BI Dax Measure- Allocate data between Range: https://youtu.be/O653vwLTUzM