Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
I have a table like this:
id | timestamp | temperature | first date - id | last date - id | middle date - id | first, last, middle |
1 | 1/1/20 1:00 AM | 14 | TRUE | FALSE | First | |
1 | 1/1/20 2:00 PM | 12.4 | FALSE | FALSE | na | |
1 | 1/1/20 8:00 AM | 16.3 | FALSE | FALSE | na | |
1 | 1/2/20 1:00 AM | 14.2 | FALSE | FALSE | na | |
1 | 1/2/20 2:00 PM | 15.9 | FALSE | FALSE | na | |
1 | 1/2/20 8:00 AM | 14.5 | FALSE | FALSE | na | |
1 | 1/3/20 1:00 AM | 13.2 | FALSE | FALSE | na | |
1 | 1/3/20 2:00 PM | 19.3 | FALSE | TRUE | Last | |
1 | 1/3/20 8:00 AM | 16.9 | FALSE | FALSE | na | |
2 | 1/1/20 1:00 AM | 14.3 | TRUE | FALSE | First | |
2 | 1/1/20 2:00 PM | 12.4 | FALSE | FALSE | na | |
2 | 1/1/20 8:00 AM | 16.3 | FALSE | FALSE | na | |
2 | 1/2/20 1:00 AM | 14.2 | FALSE | FALSE | na | |
2 | 1/2/20 1:00 PM | 15.9 | FALSE | FALSE | na | |
2 | 1/2/20 8:00 AM | 14.5 | FALSE | FALSE | na | |
2 | 1/3/20 2:00 AM | 13.2 | FALSE | FALSE | na | |
2 | 1/3/20 1:00 PM | 13.5 | FALSE | TRUE | Last | |
2 | 1/3/20 8:00 AM | 16.9 | FALSE | FALSE | na |
I have calculated the first and laste date using this formula: IF(tabledb[timestamp]=CALCULATE(MIN(tabledb[timestamp]),ALLEXCEPT('tabledb', 'tabledb'[id])),True)
How can I get the middle date in a format like last and first, and then create a new column to show first, middle and last date for every id, like shown in table above for first and last date.
Thanks a lot.
Solved! Go to Solution.
@Anonymous
I recommend you first ranked the timestamp for each id. Then use MEDIAN to get the middle rank for each id.
Rank= RANKX(FILTER(Tabledb,[id]=EARLIER(Tabledb[id])),Tabledb[timestamp],,ASC,Dense)
firstmidlast =
IF(Tabledb[Rank]=MIN(Tabledb[Rank]),"first",IF(Tabledb[Rank]=MAX(Tabledb[Rank]),"last",IF(Tabledb[Rank]=MEDIAN(Tabledb[Rank]),"mid")))
Best,
Paul
@Anonymous
I recommend you first ranked the timestamp for each id. Then use MEDIAN to get the middle rank for each id.
Rank= RANKX(FILTER(Tabledb,[id]=EARLIER(Tabledb[id])),Tabledb[timestamp],,ASC,Dense)
firstmidlast =
IF(Tabledb[Rank]=MIN(Tabledb[Rank]),"first",IF(Tabledb[Rank]=MAX(Tabledb[Rank]),"last",IF(Tabledb[Rank]=MEDIAN(Tabledb[Rank]),"mid")))
Best,
Paul
create rank inside each id. crease dense rank
refer -https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
create rank as column.
Now using that you can use min and max of rank try to find middle rank and use that.
Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/Winner-Topper-on-Map-How-to-Color-States-on-a-Map-wi...
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p...
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
89 | |
79 | |
65 | |
62 | |
60 |
User | Count |
---|---|
166 | |
114 | |
100 | |
73 | |
66 |