Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

Reply
GuidoBaggi
New Member

How do I get PowerBI to autocomplete empty time ranges from a dataset, in a visual.

I have a table called DWH_EVENT_CHANGES with basically saves records every time that occurs idEvents Status changes (idEvent status can be: 'ONLINE' , 'ARCHIVED' or 'UNAVAILABLE').  This table works in the following way: Lets take as example idEvent 10002. We want to analyze the evolution of the Status of this idEvent after 2023. As we see in the picture attached below, this idEvent in the 2023-01-17 its Status was UNAVAILABLE then it was maintained  3 days with the same status (Due to the fact that it manteined the SAME Status, we dont save those records on those days, so to save database resources) and the 2023-01-20 turned to ONLINE status. What I need to achieve in PowerBi is being able to display in a Line Chart and a Table this data "virtually autocompleted". What I mean by "virtually autocompleted" is that I need PowerBi to understand that between those 2 dates, there are 3 days in the middle were the idEvent had the same status and the visuals have to show this. Because of performance and server capacity, I dont have the possibility to save for every day and for every idEvent every record, and I can only save the dates where there was changes applied.

GuidoBaggi_0-1677842543080.png

How its currently displaid in PowerBI : 

GuidoBaggi_2-1677843237170.png

How I need it to be displayed: (in yellow the rows that should be added by PowerBI)

GuidoBaggi_3-1677843302487.png

 

 

 

 

 

2 REPLIES 2
andhiii079845
Super User
Super User

Both tables Status and date:

andhiii079845_0-1677845852442.png

andhiii079845_2-1677845908241.png

My new table:

newtable = SUMMARIZE('Date','Date'[Date],"laststatusdate",maxx(FILTER('Status','Status'[Date]<='Date'[Date]),'Status'[Date]))

 

And create a new column:

Column = CALCULATE(max('Status'[Status]),FILTER('Status','newtable'[laststatusdate]='Status'[Date]))
 
andhiii079845_3-1677845988267.png

 

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




andhiii079845
Super User
Super User

You need a second table with all dates and you have to fill a second column with the right status information from the first table. 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.