Forum Discussion
Comparing two dates using one calendar table and USERELATIONSHIP
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.