Value in dimension disappears due to measure

[JustinDallas]

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.

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.

[YoussefBelloum]

Hi,

be sure that you have this checkbox checked (on the right pane, complements area)

[rittermd]

Probably not showing because parentOrderId is null for that row.

If you don't have the box checked to show null values this row would not display.

[Digvijay_Singh · Accepted solution]

I think the measure is filtering that row, measure indicates only shows rows where parentid is equal to all possible order ids. Null in parentorderid is not a possible order id so that row is filtered.

If you write your measure like this -

SUM({<parentOrderId = P(orderId)>+<orderId = P(orderId)>} charge)

First part is same as yours one, the second part indicates, ALSO display all possible order Ids. and it shows all the rows but this may contradict your other needs.

[JustinDallas]

". Null in parentorderid is not a possible order id so that row is filtered"

That was clearly common sense, but I couldn't see the forest for the trees.