Calculated measure based on field in fact table that is not a measure in cube.

I have several fields in the fact table that are not measures in a cube. In this case I have a OrderEntryDateId, WantDateId, and InvoicedDateId. If the order has not invoiced the InvoiceDateId is 0 (zero).

My cube joins the fact table to the time dimension table on the wantDateId. The cube has measures for feet, dollars, pounds, material cost...

I want to create a calculated measure called "Invoiced Dollars" that is based on iff(invoiceDateId>0;TotalDollars,0) however invoiceDateId is not a measure nor is it linked to time in this cube.

Any ideas?

Hi. You could make the InvoiceDateID a member property of time dimension at the leaf level. You can then check the member property value in your calulated member. It might look something like this:

IIF(CINT(Time.Time.CurrentMember.Properties("InvoiceDateID")) > 0, TotalDollars, 0)

Drawbacks to this solution: (1) it only works at the leaf level of the time dimension, (2) Probably slow because of the conversion of the member property value.

Hope this helps - Paul Goldy

|||

I already played with this a bit this afternoon and it is not what I need.

I guess the best way to do this is to add a column to the fact table so it can work at all levels and be pre-aggregated.

Thanks!