Object State Display Problem by Period in Power BI

[akim_no]

I am working on a dynamic display of object states in Power BI based on a time logic, similar to snapshots taken at different periods (in this case, weeks). My source table contains, for each object, an ID, a begin date (BeginDate), and an end date (EndDate). The particularity is that the end date can be blank (null), which means the object is still ongoing, active without a defined end.

The need is to display these objects according to the periods selected by the user, typically weeks, but this can adapt to months or quarters. For each selected period (for example, a given week), I want to:

• Check if the period (week) is between the object's begin date and end date.

• If the end date is blank, consider the object as active over all periods from its begin date, so it should appear for all selected periods.

• Otherwise, display the object only if the selected period falls within the [begin date, end date] interval.

Concretely, if a user selects multiple weeks, an object with a blank end date should appear as many times as there are selected weeks, causing an implicit duplication of rows in the display to represent this continuous state. For objects with a defined end date, they appear only for the periods during which they were active.

I would like to know if it is possible to implement this logic without creating a new table that duplicates the rows for each week or month. Also, this table is just an example because I need to apply the same logic to other tables as well.

Source : 

ID	BeginDate	EndDate
A123	01/01/2025	(Blank) => in progress
A423	20/01/2025	(Blank) => in progress
B456	25/07/2025	29/07/2025
B479	25/07/2025	21/11/2025

Filtre : 

W1-2025

W2-2025

W3-2025

W4-2025

…

What I'm looking for : 

Use cas 1 :	W1-2025
ID	BeginDate	EndDate
A123	01/01/2025	(Blank)
Use cas 2 :	W4-2025
ID	BeginDate	EndDate
A123	01/01/2025	(Blank)
A423	20/01/2025	(Blank)

Use cas 3:	W31-2025
ID	BeginDate	EndDate
A123	01/01/2025	(Blank)
A423	20/01/2025	(Blank)
B456	25/07/2025	29/07/2025
B479	25/07/2025	21/11/2025
Use cas 4:	W40-2025
ID	BeginDate	EndDate
A123	01/01/2025	(Blank)
A423	20/01/2025	(Blank)
B479	25/07/2025	21/11/2025

Use cas 4:	W4-2025, W40-2025
ID	BeginDate	EndDate
A123	01/01/2025	(Blank)
A423	20/01/2025	(Blank)
B479	25/07/2025	21/11/2025

A123	01/01/2025	(Blank)
A423	20/01/2025	(Blank)

[v-sdhruv]

Hi @akim_no ,

I hope the explanation helps you address the query. If you have any question, feel free to reach out.

Thank you for reaching out on Microsoft Community Forum

[v-sdhruv · Accepted solution]

Hi @akim_no ,

Up to 10 million rows it is generally manageable with good model design.
Hope this clarifies!

[akim_no]

Creating a measure, placing it in the table, and then making it invisible does not meet my needs.
So I had to duplicate my rows and generate all possible combinations based on the Weeks I have.
This results in a very large table.
Therefore, I want to check Power BI's limitations to understand at what data volume it will start to struggle or fail.

[v-sdhruv]

Hi @akim_no ,

Just wanted to check if you had the opportunity to review the explaination provided by the super user?
If you need any assistance , let us know.

Thank You

[v-sdhruv]

Hi @akim_no ,

Just wanted to check if you had the opportunity to review the explaination provided by the super user?
If you need any assistance , let us know.
Thank you @johnt75  for sharing your inputs.

[akim_no]

@johnt75, I mean, is there another solution or a setting that would allow us to keep the row duplication and week distinction without having to explicitly add the measure and the week to every table or matrix visual? Otherwise, it becomes slow and cumbersome we’d have to manually add both the measure and the week to every new visual, which isn’t ideal.

[johnt75]

No, you will need some additional table and corresponding measure to enable this. As there is only 1 row in the underlying data then only 1 row would be shown. You need some other dimension to generate the dates which you can then use to show multiple copies of the same row.

[johnt75]

You need a proper date table, There's plenty of articles, videos and tools which can help create one.

In your case, do not create a relationship from the date table to the fact table, as the facts relate to date ranges rather than specific dates.

[akim_no]

@johnt75, Actually, I don’t have a week column in my fact tables — I derive it from the start and end dates.

My issue with the measure that’s supposed to duplicate rows is that it doesn’t actually do that. For example, if I have two IDs that are present in two different weeks, they should appear twice: one row for the first week, another for the second. And if I select more weeks, the row should be duplicated as many times as there are relevant weeks — but that’s not the case.

[johnt75]

To force the duplication of rows, one per week, you would need to include the week column or similar in the table / matrix values. You don't necessarily need to make it visible, you could set the column width to 0.

You can create a measure like

Row is visible =
VAR MinDate =
    MIN ( 'Date'[Date] )
VAR MaxDate =
    MAX ( 'Date'[Date] )
VAR BeginDate =
    SELECTEDVALUE ( 'Table'[BeginDate] )
VAR EndDate =
    SELECTEDVALUE ( 'Table'[EndDate] )
VAR Result =
    IF ( BeginDate <= MinDate && ( ISBLANK ( EndDate ) || EndDate >= MaxDate ), 1 )
RETURN
    Result

Add this as a filter to the table / matrix, set to show only when the value is 1