Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hello, I am looking for some assistance with finding the latest date for both On and Off times for each ID where there can be mulitple records of both Types for each ID.
Date | Type | ID | Latest On Time | Latest Off Time |
1/1/22 12:15 | on | 1 | ||
1/2/22 12:30 | on | 1 | ||
1/4/22 16:15 | off | 1 | ||
1/2/22 18:15 | off | 1 | ||
1/5/22 12:15 | on | 2 | ||
1/6/22 12:15 | off | 2 | ||
1/7/22 12:15 | on | 2 | ||
1/8/22 12:15 | on | 3 | ||
1/9/22 12:15 | on | 3 | ||
1/10/22 12:15 | on | 4 | ||
1/11/22 12:15 | off | 4 |
Solved! Go to Solution.
Hi @dschul365
please try
Latest On Time =
MAXX (
FILTER (
CALCULATETABLE ( 'Table', ALLEXCEPT ( 'Table', 'Table'[ID] ) ),
'Table'[Type] = "on"
),
'Table'[Date]
)
You are correct, the latest date values are now populating. Thank you for the assist!
Hi @dschul365
please try
Latest On Time =
MAXX (
FILTER (
CALCULATETABLE ( 'Table', ALLEXCEPT ( 'Table', 'Table'[ID] ) ),
'Table'[Type] = "on"
),
'Table'[Date]
)
Hello @tamerj1
Unfortunately, once applied this formula only returns blank values within the Latest On Time column. Could you explain how the ALLEXCEPT forumla is intened to work here?
The CALCULATETABLE with ALLEXCEPT should return the complete table of the current ID. This should work if Type is a column rather than a measure. Is it?
aslo please try
Latest On Time =
MAXX (
FILTER (
CALCULATETABLE ( 'Table', REMOVEFILTERS (), VALUES ( 'Table'[ID] ) ),
'Table'[Type] = "on"
),
'Table'[Date]
)
Check out the July 2025 Power BI update to learn about new features.
User | Count |
---|---|
23 | |
8 | |
7 | |
6 | |
6 |
User | Count |
---|---|
28 | |
12 | |
10 | |
10 | |
6 |