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

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.

Helper I
Helper I

URGENT!! Classify data of multiple records into buckets using Measure

Hi! Urgent help required. I have a dataset with columns [RO_Key], [Status], [Valid From Date] and [Valid To Date]. The thing about this dataset is that there might be multiple lines of each [RO_Key] (i.e a few contracts per RO_Key)



My goal is to classify each unique [RO_Key] into either "Pre-committed" / "Committed" / "Future Vacant" based on the selected date in the report ( I created a separate calendar table and a dax measure to filter out where selected date is within valid from and to date)


The logic should be as follows:


1. "Committed" definition --> "Occupied" (for date selected) AND IF there is a next record with a greater [Valid From Date] beyond date selected with [Status] = "Occupied" 


e.g. Date selected = 31 Mar 2022, corresponds to row 5 which has [Status] = "Occupied, and the next record row 6 has [Valid From Date] = 1 May 2022 with [Status] = "Occupied", therefore [Status3] of row 5 should be "Committed".



2. "Pre-committed" Definition: Same as "Committed", except that [Status] = "Vacant" (for date selected) (i.e currently vacant)


3. "Future Vacant": If the next row (assuming date is sorted in ascending order) is MAX([Valid From Date]) of the particular RO_KEY AND next row has [Status] = "Vacant".




My eventual goal is to have just the [RO_KEY] and [Status3] columns, with [Status3] dynamically updating depending on the date I selected.



Any help would be supppppppppper appreciated. Attached is the PBI file.


Perhaps this line of thinking would help: If the selected date falls under one period/record, obtain the rank and status of that line item, find the next line item of the [RO_Key], using rank + 1, and find the status of that line item.. --> but i havent been able to crack it sigh 😞 or would creating a virtual table help??




Helpful resources

Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City


Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.