Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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.
Hi,
be sure that you have this checkbox checked (on the right pane, complements area)
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.
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.
". 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.