Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I am trying to create a measure that generates daily data showing what Status an ID is in, when the source data itself does not exist in a daily matter. Ultimately, I want to show how many records were in each state on each day of a specified time period as well as a table showing the status for each day.
Sample Source Data & Expected Results
To accomplish this, I am trying to use the RANKX function to determine what is the most recent state to the day being evaluated. I am having trouble getting the syntax to give the results I am looking for. I have attached some source sample data as well as the Expected results.
Below is one attempt I've made, where _date is coming from a seperate Date Table that has no relationship to my Table. I am able to get the proper Table (via the Filter expression in the first argument) to be used in RANKX, its the 2nd Expression argument that I am having trouble with.
Rank =
var _date = MAX(Calendar[Date])
var _id = MAX(Table[ID])
Return
RANKX(
FILTER(
Table,
Table[ChangedDate']<=_date && Table[ID]=_id),
CALCULATE(
SUM(ChangedDate)),,
ASC)
Solved! Go to Solution.
Hi @EaglesFly123 ,
Here are the steps you can follow:
Show how many records there are in each state every day in the specified time period:
1. Create calculated table.
Table 2 =
SUMMARIZE('Table','Table'[Date],
"New",CALCULATE(COUNT('Table'[WorkItem ID]),FILTER(ALL('Table'),'Table'[Date]=MAX('Table'[Date])&&'Table'[State]="New")),
"Reay",CALCULATE(COUNT('Table'[WorkItem ID]),FILTER(ALL('Table'),'Table'[Date]=MAX('Table'[Date])&&'Table'[State]="Ready")),
"Active",CALCULATE(COUNT('Table'[WorkItem ID]),FILTER(ALL('Table'),'Table'[Date]=MAX('Table'[Date])&&'Table'[State]="Active")),
"Test",CALCULATE(COUNT('Table'[WorkItem ID]),FILTER(ALL('Table'),'Table'[Date]=MAX('Table'[Date])&&'Table'[State]="Test")),
"Done",CALCULATE(COUNT('Table'[WorkItem ID]),FILTER(ALL('Table'),'Table'[Date]=MAX('Table'[Date])&&'Table'[State]="Done"))
)
2. Result:
Display the latest status every day:
Is your latest status based on the largest ID and grouping by date?
1. Create calculated column.
rank =
var _rank=RANKX(FILTER(ALL('Table'),'Table'[Date]=EARLIER('Table'[Date])),'Table'[WorkItem ID],,ASC)
return
_rank
new staus =
CALCULATE(MAX('Table'[State]),FILTER(ALL('Table'),'Table'[rank]=MAX('Table'[rank])&&'Table'[Date]=EARLIER('Table'[Date])) )
2. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @EaglesFly123 ,
Here are the steps you can follow:
Show how many records there are in each state every day in the specified time period:
1. Create calculated table.
Table 2 =
SUMMARIZE('Table','Table'[Date],
"New",CALCULATE(COUNT('Table'[WorkItem ID]),FILTER(ALL('Table'),'Table'[Date]=MAX('Table'[Date])&&'Table'[State]="New")),
"Reay",CALCULATE(COUNT('Table'[WorkItem ID]),FILTER(ALL('Table'),'Table'[Date]=MAX('Table'[Date])&&'Table'[State]="Ready")),
"Active",CALCULATE(COUNT('Table'[WorkItem ID]),FILTER(ALL('Table'),'Table'[Date]=MAX('Table'[Date])&&'Table'[State]="Active")),
"Test",CALCULATE(COUNT('Table'[WorkItem ID]),FILTER(ALL('Table'),'Table'[Date]=MAX('Table'[Date])&&'Table'[State]="Test")),
"Done",CALCULATE(COUNT('Table'[WorkItem ID]),FILTER(ALL('Table'),'Table'[Date]=MAX('Table'[Date])&&'Table'[State]="Done"))
)
2. Result:
Display the latest status every day:
Is your latest status based on the largest ID and grouping by date?
1. Create calculated column.
rank =
var _rank=RANKX(FILTER(ALL('Table'),'Table'[Date]=EARLIER('Table'[Date])),'Table'[WorkItem ID],,ASC)
return
_rank
new staus =
CALCULATE(MAX('Table'[State]),FILTER(ALL('Table'),'Table'[rank]=MAX('Table'[rank])&&'Table'[Date]=EARLIER('Table'[Date])) )
2. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |