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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
tiffanyt123
Helper I
Helper I

Need help with DAX measure with filters for unique keys (Real Estate Data)

This project is regarding Real Estate Data.

 

For a particular unique key, I would like to fill up the [Status3] column with 1) "Committed", 2) "Pre-committed" or 3) "Future Vacant", depending on the date selected from a calendar table.

 

The logic should be as follows:

"Committed": [Status] = "Occupied" (for date selected) AND [Status] = "Occupied" (for the next record with [Valid From Date] beyond date selected)

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".

tiffanyt123_0-1674986859673.png

 

"Pre-committed": Same as "Committed", except that [Status] = "Vacant" (for date selected).

tiffanyt123_2-1674987414824.png

"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".

tiffanyt123_1-1674987273685.png

 

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

tiffanyt123_3-1674988026598.png

 

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

 

https://app.box.com/s/7k9yxdigd8scpb1t67xoa7m1eye4di8g

 

2 REPLIES 2
TonyZhou1980
Resolver I
Resolver I

Hi Tiffany,

 

Do you have other ways to upload the sample file for downloading and review?

 

Thank you,

Tony

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

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