4 Replies Latest reply: Feb 14, 2018 12:31 PM by Justin Dallas RSS

    Value in dimension disappears due to measure

    Justin Dallas

      TL;DR How do I make a dimension show when it's measure is null/undefined

       

      Hello Folks,

       

      I have a table with three dimensions (OrderId, Charge, ParentOrderId) and a Measure (=SUM({1<parentOrderId = P({$}orderId)>} charge)).  My data has a row whereby the ParentOrderId is null, and for whatever reason, that Order doesn't appear in my table unless I delete my measure.  Why is this?  I've never thought of it till now, so I assume it was just something I knew without asking questions.

       

       

      RowCannibalism.PNG

       

      My test data looks like so:

       

      Orders:
      //Getting a null while doing an inline Load is difficult,
      //so I have to do magic like this
      LOAD orderId, If(parentOrderId = '-', Null(), parentOrderId ) AS 'parentOrderId'
      ;
      LOAD * Inline
      [
      orderId, parentOrderId
          1, '-'
          2, 1
          3, 1
      ]
      ;
      
      
      Invoices:
      LOAD * Inline
      [
      orderId, charge
          1 , 150
          2 , 50
          3 , 25
      ]
      ;
      

       

       

      Any explanation is welcome.