Forum Discussion
Working with multiple date keys in table
In DAX you can use the same approach.
Measure = AVERAGEX('DWH DIM_CLAIM', DATEVALUE(Left([CloseDateKey],4) & "-" & MID([CloseDateKey],5,2) & "-" & RIGHT([CloseDateKey],2)) -DATEVALUE(Left([CreateDateKey],4) & "-" & MID([CreateDateKey],5,2) & "-" & RIGHT([CreateDateKey],2)))
Hey lbendlin,
Thanks for your answer!
That is the whole thing, that i do need a full usage of DAX without creating calculating columns, only based on measures. PQ solution does not meet my performance expectations 😕
And the output required is simple card visual with Average time or sth like : 6.7 Days
In DAX you can use the same approach.
Measure = AVERAGEX('DWH DIM_CLAIM',
DATEVALUE(Left([CloseDateKey],4) & "-" & MID([CloseDateKey],5,2) & "-" & RIGHT([CloseDateKey],2))
-DATEVALUE(Left([CreateDateKey],4) & "-" & MID([CreateDateKey],5,2) & "-" & RIGHT([CreateDateKey],2)))lbendlin ,
That works perfectly fine! Datevalue was a function i did not know about.
One more thing is that there is a reason that Calendar table exists.
Is there an approach, where we can use the Calendar table in order to actually work on relationships/Keys. It is just due to some CloseDateKey's that are not date yet (-1 key is a blank value in calendar table, meaning something did not Finish/Close yet). What do you think?
If it does not sound possible/reasonable i will accept ur solution above.
I believe that I could also make a condition with ur latest measure to avoid counting if CloseDateKey is -1, but for some learning purposes i'd like to know if there is a way to perform this using keys/relationships as mentioned paragraph above.This is going into outer join and USERELATIONSHIP territory. Without good sample data ( and a bit more context on what you are trying to achieve and what the expected result should look like ) it will be difficult to help more.
Sure