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.
Hello - I am creating a new report in PowerBi to try and capture the amount of time it takes my team to lease and build out a commerical space. Eventually, I will want to filter this list into two buckets, if the space ever had a tenant in it or if the space was being re-tenanted.
I am trying to write a new column that will determine if the space is being re-tenanted, and I think the best way to solve for this is to review the SpaceID (my unique identifier for each leasable space) and determine if that SpaceID shows up in another row in the data. If it does show up in the data set multiple times, the first lease date would be the initial buildout, and every other occurance would be retenanting. Below is a sample of my data. The space ID 32142901 with a lease date of 8/28/2020 should show as a retenanting and 32142901 with a lease date of 3/15/18 should be intial buildout.
Thanks for the help!
Solved! Go to Solution.
pls try this
Column =
VAR _min=CALCULATE(min('Table'[Leasing Date]),ALLEXCEPT('Table','Table'[SpaceID]))
VAR _max=CALCULATE(max('Table'[Leasing Date]),ALLEXCEPT('Table','Table'[SpaceID]))
return if('Table'[Leasing Date]=_min,"Initial Buildout",if('Table'[Leasing Date]=_max,"Retananting"))
Proud to be a Super User!
The Buildout Type is the output column I would like to replicate in PowerBI.
pls try this
Column =
VAR _min=CALCULATE(min('Table'[Leasing Date]),ALLEXCEPT('Table','Table'[SpaceID]))
VAR _max=CALCULATE(max('Table'[Leasing Date]),ALLEXCEPT('Table','Table'[SpaceID]))
return if('Table'[Leasing Date]=_min,"Initial Buildout",if('Table'[Leasing Date]=_max,"Retananting"))
Proud to be a Super User!
That was it! Thank you!!!!
you are welcome
Proud to be a Super User!
Where I'm getting hung up is that the expected output is dependent upone other rows within the table. Starting at the top of the sampled data set, the expected output is as follows:
Hopefully that clarifies what I'm trying to solve for.
Thanks for the help!
could you pls show the expected output in excel and provide the screenshot?
Proud to be a Super User!
what's the expected output?
So the logic is mininum lease date and maximum tenant opening?
maybe try
=min([table[lease date])
=max(table(tenant opening])
Proud to be a Super User!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
94 | |
83 | |
67 | |
59 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |