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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
ManuApo
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
M

5 REPLIES 5
ManuApo
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. 

Greg_Deckler
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])
RETURN
  __Result

@ 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!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors