Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Use DAX or Power Query to create a calculated column

 I have a table called Address as shown below I want to use power query or dax to create calculated column to achieve AddressStatus (current and previous address) as shown below How can...
Need Help
Show and Tell
Tips and Tricks
  • vanessafvg's avatar
    vanessafvg
    4 years ago

    ok so if there is no rule, what is the rule you want  to select the data, just the first one in sequential order? 

     

    I would add an index in the transformation layer (power query), under add column / index column

     

     

    and then create a dax measure

     

    addressstatus =
    VAR person =
        SELECTEDVALUE ( 'table'[fullname] )
    VAR currindex =
        SELECTEDVALUE ( 'table'[idcolumn] )
    VAR minid =
        CALCULATE ( min ( 'table'[idcolumn] ), 'table'[fullname] = person )
    VAR status =
        IF ( currenindex = minid, "Current Address", "PreviousAddress" )
    RETURN
        status

az38's avatar
az38
Community Champion
4 years ago

Hi Anonymous 

what is the rule to define the current address? any indexes or dates or smth else?

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    az38 - there is no rule that define current address. The table was parsed on JSON, no index, no date. 

    • vanessafvg's avatar
      vanessafvg
      Community Champion
      4 years ago

      ok so if there is no rule, what is the rule you want  to select the data, just the first one in sequential order? 

       

      I would add an index in the transformation layer (power query), under add column / index column

       

       

      and then create a dax measure

       

      addressstatus =
      VAR person =
          SELECTEDVALUE ( 'table'[fullname] )
      VAR currindex =
          SELECTEDVALUE ( 'table'[idcolumn] )
      VAR minid =
          CALCULATE ( min ( 'table'[idcolumn] ), 'table'[fullname] = person )
      VAR status =
          IF ( currenindex = minid, "Current Address", "PreviousAddress" )
      RETURN
          status