Determine Latest (MAX) Value by Index by Group

[Anonymous]

I have a dataset with several tables:

• Fact Table: Spaces
  

  ID	Name	Stage ID	Building ID	Area
  1	Room1	1	1	100
  2	Room1	2	1	125
  3	Room2	1	1	200
  4	Room2	2	1	180
  5	Room3	1	1	300
  6	Room3	2	1	350
  7	Room 1	1	2	100
  8	Room 2	1	2	200
  9	Room 3	1	2	315

• Dim Tables:
  • Building
    

    ID	Name
    1	Building1
    2	Building2

  • Stage
    

    ID	Name	Index
    1	Stage1	1
    2	Stage2	2

1. Should I consider break out "Room" as a Dimension to a Fact Table which is only rows of different values for Room over each stage?

I'm trying to write a measure that will report the "Current Stage Value" such that a Table Visual might report this:

Building Name	[Current Stage]	[Sum Area]	[Variance from Previous Stage]
Building1	Stage2	655	55
Building2	Stage1	615

I feel like I should be able to do this, but clearly stumbling. All the MAX examples I can find work from a single table, and many are often done as a Column and not a Measure. I'd prefer a Measure since it will react dynamically to filtering of stages, which would be desired, so that the "Max" Stage in the current context is always returned as the "Current" stage.

Thanks in advance!

[Jihwan_Kim · Accepted solution]

https://www.dropbox.com/s/j4f2frdwzvvvg33/rpiboy.pbix?dl=0 

[Anonymous]

@Jihwan_Kim thanks for the solution, I mostly follow it, but I was hoping you could explain the Current Stage Measure a bit more?

• I see that you're getting the Max index in the current context
• I understand that calculate table basically returns a table with a single row, but I'm not clear on how the IF and HASONEVALUE is fitting in. It seems like you're making the assumption there will always be one value and there is no else clause defined for the IF.

Thank you,

-Robert

[Anonymous]

@Jihwan_Kim this looks great! Will dig in a bit more tomorrow. Thank you very much. For some reason I had in my head that RELATED could only be used in Calculated Columns and not Measures! Clearly I was mistaken, argh!