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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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

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

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.