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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.