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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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

Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.