Forum Discussion
Comparing two dates using one calendar table and USERELATIONSHIP
just to clarify: you state "two dates in a single measure" and then
I have two tables:
1) Fact Sales ([Order ID], [Est Delivery Date ID], [Delivery Date ID])
2) Dim Date ([D Date ID], [D Date])
..... I've underlined the terms that are confusing me....
Is there sequentiality of the Dim Date ID such that you don't need the actual date itself but simply just their comparitive relative values i.e. So that you seek a record count of Table 1 where DD > EDD ?
Hi Cahaba,
Thanks for your reply. Sorry for the confusion - yes, your interpretation is correct.
I have two tables (1 fact table, 1 calendar table date dimension).
The fact table has two date IDs ([estimate delivery date id] (int), [delivery date id] (int)) which both get their date infomtion via the calendar table dimension. (The two date ids are related to the calendar table using one active relationship [delivery date id] and one inactive relationship [estimated delivery date id]). In other examples I've read, people have used USERELATIONSHIP() to relate a single calendar table to multiple date fileds.
Without creating a calculated column in the fact table, I'd like to create a measure in the fact table that evaluates a count of orders (rows) where the [delivery date] >[estimated delivery date] for any date period.
Hope that makes more sense! :)
Pbix
Rowwise comparison of columns is very expensive at runtime. I suggest you simply add a calculated column called "Late" and add a forumula somthing like this
=if(FactTable[Del Date] > FactTable[Est Del Date],"True")
You can then use this column in your measures.
Late orders = calculate(countrows(FactTable),FactTable[Late] = "True")
Hi Matt,
Thanks for your reply :)
Yes, was thinking about adding a helper column to do this (I'd previously read that calc columns are also quite expensive) so this might be the route to go down.
Is it possible at all to do it via a measure? I'm looking to learn how to compare different date values from different tables as I suspect I'll have to do this alot - without always needing to generate a calculated column. Is it possible to compare two actual dates in the related calendar table, where the dates both reference the same calendar table via two individual relationships??
Thanks!
Pbix
Calculated colums can be expensive but only if it has a high cardinality. You can read my guide here
http://exceleratorbi.com.au/calculated-columns-vs-measures-dax/
Read the section under general rules when calc columns are OK.
Yes you can do this via a measure, but as I mentioned above, rowwise comparison is very expensive. Power Pivot has 2 engines, a Storage Engine (SE) and a Formula Engine (FE). SE is fast, compressed, multi threaded and cached. FE is slow, can't use compressed data, single threaded and not cached. Rowwise comparisons like you mention will be forced to use the formula engine which is not ideal. However if you have a small data set and you are happy with the peformance then you can do it in a measure. But why do you want to?
Also, why do you want to use your calendar table to compare the dates? The data is in the fact table so there is no need to reference the calendar table.
Thanks MattAllington. Really helpful.
part of my question was whether that Date ID was sequential.
if so - then you don't even have to join to the Dim Date table - you can just compare the relative values in Table 1.
Hi CahabaData
Thanks - yes - date ids are sequential so this is something I considered doing. Just two small issues for me -
1) Where the EDD/DD is unknown then the dateid = -1 (so would need some row evaluation to check if dateid <>-1
2) to do similar (But with the actual dates) I tried returning, for each row, the actual date value for each dateid using something along the lines of:
CALCULATE(VALUES(Dim Date[Date]), USERELATIONSHIP(fact[dateid],Dim Date[dateid])
But, for a reason I haven't yet worked out, the above formula didn't always return the correct EDD/DD date for each dateid - does this formula look correct?
Also - just learning how to relate fields together so v keen to learn about doing this via a measure!
Thanks :)
Pbix
