Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Values form multiple rows to one row

Hello, I would like to have values from multiple rows into one row.   For this ID i have multiple dates in multiple rows which i would like to have in one row.   I would like to do this in...
DAX
multiple rows
one row
  • Mariusz's avatar
    Mariusz
    6 years ago

    Hi Anonymous 

     

    You can create a Table using DAX like below.

    Table 2 = 
ADDCOLUMNS(
    VALUES( 'Table'[ID] ),
    "Date1", CALCULATE( MAX( 'Table'[Date1] ) ),
    "Date2", CALCULATE( MAX( 'Table'[Date2] ) ),
    "Date3", CALCULATE( MAX( 'Table'[Date3] ) )
)

     

    Best Regards,
    Mariusz

    If this post helps, then please consider Accepting it as the solution.

    Please feel free to connect with me.
    Mariusz Repczynski

     

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Please help how can i do this scenario.

placesAMA00241_mangoes_rawAMA01234_banana_rawAMA04545_apples_rawTypeID
TexasAvailableAvailable Oragnic fruitA0001
VirginiaAvailableAvailable Oragnic fruitA0001
MDAvailable AvailableOragnic fruitA0001
DCAvailableAvailable Oragnic fruitA0001
VAAvailableAvailableAvailableOragnic fruitA0001
CAAvailableAvailable Regular FruitB0001
NJAvailableAvailable Regular FruitB0001
NYAvailable AvailableRegular FruitB0001
DEAvailableAvailable Regular FruitB0001
PAAvailableAvailableAvailableRegular FruitB0001

Expected Results:

AMA00241_mangoes_rawAMA01234_banana_rawAMA04545_apples_rawTypeID
AvailableAvailableAvailableOragnic fruitA0001
AvailableAvailableAvailableRegular FruitB0001

 

The column names contains like

month 1 - AMA00241_mangoes_raw, AMA01234_banana_raw,AMA04545_apples_raw, Type,ID,places

month2 - AMA03432_mangoes_ft, AMA01434_banana_ft,AMA04535_apples_ft, AMA09095_grapes_ft,AMA0005_pear_ft,Type,ID,places

month3 - AMA03345_peach_raw, AMA00034_orange_ft,Type,ID,places

The dynamic cloumn name contains like AMA0%