- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
I hope I explain well my self, anyway I can clarify it.
Thanks so much
M
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi @Greg_Deckler thanks for your reply and your time.
Unfortunately it didn't work.
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi @Greg_Deckler I am not sure if you saw my last reply. Hope you can help me with this.
Thanks in advance!!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@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!: Power BI Cookbook Third Edition (Color)
DAX is easy, CALCULATE makes DAX hard...
Helpful resources
Join us at the Microsoft Fabric Community Conference
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Microsoft Fabric Community Conference 2025
Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.
Subject | Author | Posted | |
---|---|---|---|
04-12-2024 01:57 AM | |||
07-16-2024 02:24 PM | |||
07-17-2024 04:48 AM | |||
06-18-2024 06:16 AM | |||
09-04-2023 10:03 PM |
User | Count |
---|---|
21 | |
17 | |
12 | |
6 | |
5 |
User | Count |
---|---|
29 | |
25 | |
20 | |
13 | |
10 |