10 Replies Latest reply on May 4, 2018 8:01 AM by David Luhut

    Compare MOST (IF NOT ALL) fields of the same fact table and return the result

    David Luhut



      Assume I have the following fact table


      Profit CenterDateGLFAAmount
      10010100Jan 2018DZ110
      10010100Jan 2018AZ15
      10010100Jan 2018BZ120
      10010100Jan 2018CZ115
      10010100Jan 2018DZ15
      10010100Jan 2018AZ125
      10010100Jan 2018BZ1




      And After a JOIN with another table, I "added" 2 of the following columns [Category] and [SubCategory]


      Profit CenterDateGLFACategorySub CategoryAmount
      10010100Jan 2018AZ1RevPAS
      10010100Jan 2018BZ1RevPAS
      10010100Jan 2018CZ1RevPAS
      10010100Jan 2018DZ1RevenueGross Rent10
      10010100Jan 2018AZ1RevenueVacancy5
      10010100Jan 2018BZ1RevenueOther20
      10010100Jan 2018CZ1RevenueGross Rent15
      10010100Jan 2018DZ1RevenueVacancy5
      10010100Jan 2018AZ1RevenueOther25
      10010100Jan 2018BZ1RevenueGross Rent20


      How can I do a Calculated Field that return the value from the fact Table itself, whenever the [Category] is 'RevPAS', and that all of the fields (before the JOIN), in this case except 'Category' and 'SubCategory', is the same.


      For example, looking at the 1st row, it is 'RevPAS', thus lookup the table and search for [Category] = 'Revenue'  AND  ([Sub Category] = 'Gross Rent'  OR  [Sub Category] = 'Vacancy') AND IF ALL FIELDS of the fact Table which is [Profit Center] to [FA] are the same, then return the [Amount].



      Desired Result


      Profit CenterDateGLFACategorySub CategoryAmountCalculated Field Result
      10010100Jan 2018AZ1RevPAS5
      10010100Jan 2018BZ1RevPAS20
      10010100Jan 2018CZ1RevPAS15
      10010100Jan 2018DZ1RevenueGross Rent10Null
      10010100Jan 2018AZ1RevenueVacancy5Null
      10010100Jan 2018BZ1RevenueOther20Null
      10010100Jan 2018CZ1RevenueGross Rent15Null
      10010100Jan 2018DZ1RevenueVacancy5Null
      10010100Jan 2018AZ1RevenueOther25Null
      10010100Jan 2018BZ1RevenueGross Rent20Null