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

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