Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Working with multiple date keys in table

Hello,   I am having in table A two date keys formatted as YYYYMMDD (CreateDateKey and CloseDateKey). There is another entity called Calendar with DateKey formatted also YYYYMMDD and Date in dat...
Need Help
Tips and Tricks
  • lbendlin's avatar
    lbendlin
    4 years ago

    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)))
Anonymous's avatar
Anonymous
Not applicable
4 years ago

Hi lbendlin ,

Thanks for your response.

Ive updated post message above with sample data and expected output.

Thanks for your help!

lbendlin's avatar
lbendlin
Super User
4 years ago

Sorry for mentioning USERELATIONSHIP - it is not required at all.  You can do the math in Power Query

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTIyMDIyMDQyQDCNlGJ1opWM4AIGpsjKQHLGCDkzdDkTuIChCbocwiBDDDMRBhlimGmOkLNEkYsFAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Primary Key" = _t, CreateDateKey = _t, CloseDateKey = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each Date.From([CloseDateKey])-Date.From([CreateDateKey])),
    #"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"Custom", Int64.Type}})
in
    #"Changed Type"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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

    • lbendlin's avatar
      lbendlin
      Super User
      4 years ago

      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)))
      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        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.