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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper II
Helper II

Struggling to create Measure :( Please help!!

Hi community, 

I am really struggling to create a measure that basically shows the StatusID every day in a date calendar until it changes. My dataset has some IDs with different StatusID that changes along the time. The idea is to create a gantt chart where i can track along a month how often each ID changes its status. 

I created a dimension calendar table to have all the dates, as my fact table doesn't have all the dates.
Each row in my Fact table will have ID, StatusID and Date.  When an ID changes the status it will create a new row with a different StatusID an a new Date

I attach a picture of how it looks right now: 

- I have a date slicer on top of this matrix filtering the last 7 days.
- The matrix has ID as rows, Date from the dimension Calendar table as columns and StatusID as values.

- My objective is to create a measure that shows the max StatusID for each day of the dimension Calendar table

For example, the first rows of the table below will show 1s until the 25th of October where the StatusID will change to 2 and it will show a 2 until the 31st of October.

Screenshot 2022-11-02 150903.jpg

I hope I explain well my self, anyway I can clarify it. 

Thanks so much

Helper II
Helper II

Hi   @Greg_Deckler  thanks for your reply and your time. 

Unfortunately it didn't work. 

Screenshot 2022-11-02 150903.jpg

I want my measure to fill up every cell in the matrix with the current status for every calendar date until it changes. The end point will be to apply a background color to each status ID so I can track the changes easily.

Any ideas? 

FYI - I have removed the ALL function from the formula because I am appliying a filter on the page. 

Hi @Greg_Deckler  I am not sure if you saw my last reply. Hope you can help me with this. 

Thanks in advance!!

Hi, @ManuApo 

Are you referring to using the state ID to fill all nulls of the matrix?

Not fully sure about your needs, please share a sample file for further research.

Best Regards,
Community Support Team _ Eason


Hello @v-easonf-msft 

Thanks for your reply! Yes that is what I am after. I want to fill all null values with the corresponding state ID on each date. 

Super User
Super User

@ManuApo Try this:

Measure = 
  VAR __Date = MAX('Calendar'[Date])
  VAR __ID = MAX('Facts'[ID])
  VAR __Last = MAXX(FILTER(ALL('Facts'),[ID] = __ID && [Date]<= __Date),[Date])
  VAR __Result = MAXX(FILTER(ALL('Facts'),[ID] = __ID && [Date] = __Last),[Status])

Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.


Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.