Using MAX Function Across two columns

[bhmiller89]

I have parking lot occupancy data and the data is captured multiple times a day. I want to see the most recent data. I created a column, "LastSampleDate" To show the most recent date/time a sample was captured. However, I can't figure out how to show the #Occupied Spaces. Example: the "Brooke" location had occupied spaces samples throughout the day but at the last sampling, 8:42:25PM, there were only 140 spaces occupied but the data is summing everything for the whole day

[Pulkit]

Choose the latest() function for the datetime column, this will give you only the latest result for each location.

[Ashish_Mathur]

Hi,

You do not need a Lastsampledate column in your dataset.  Try these measures

Last Sample Date = MAX(Data[DSample])

Occupied spaces on last sample date = LOOKUPVALUE(Data[OccupiedSpaces],Data[DSample],[Last Sample Date],Data[Location],Data[Location])

Hope this helps.

[bhmiller89]

@Ashish_Mathur the only issue is it won't work for each location, the measure only seems to work if I type in a location.  There are 4 locations total, I was just using "Brooke" as an example. This is what works but how do I make it work for all locations?

LastOccupiedSpaces = LOOKUPVALUE(ParkingLotSample[OccupiedSpaces], ParkingLotSample[DSample], [LastSampleD], ParkingLot[Location], "Brooke")

[Ashish_Mathur]

Why are you hard coding Brooke in the DAX formula.  I have not done that in mine.  Try this

=LastOccupiedSpaces = LOOKUPVALUE(ParkingLotSample[OccupiedSpaces], ParkingLotSample[DSample], [LastSampleD], ParkingLot[Location],ParkingLot[Location])

[bhmiller89]

@Ashish_Mathur it won't allow me to do it that way

[Ashish_Mathur]

Hi,

Try this

=LastOccupiedSpaces = LOOKUPVALUE(ParkingLotSample[OccupiedSpaces], ParkingLotSample[DSample], [LastSampleD], ParkingLot[Location],VALUES(ParkingLot[Location]))

[bhmiller89]

It didn't like that either It returned 

"Couldnl't Load the data for this visual

MdxScript(Model) (7,156) Calculation error in measure 'ParkingLotSample'[LastOccupiedSpaced]: A table of multiple values was supplied where a single value was expected"

[Ashish_Mathur]

Hi,

Share the link from where i can download the file.

[bhmiller89]

https://www.dropbox.com/s/xeatcgw31w6w1mt/Parking%20Lot%20Data.pbix?dl=0

[Ashish_Mathur]

Hi,

I received absolutely no error.  I just ticked the box for LastOccupiedSpaces.

[bhmiller89]

Ugh it still won't work for me! I don't know why. Thanks for all of your help!

[Ashish_Mathur · Accepted solution]

Hi,

In the visualisation pane, click on the roller brush icon and under Totals, turn off the Totals.  Now drag the measure in the visual.

Hope this helps.