topic Re: Values form multiple rows to one row in DAX Commands and Tips

Values form multiple rows to one row

Anonymous — Fri, 01 Nov 2019 09:57:21 GMT

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, because i filter these dates in multiple columns after i imported the data.

Anyone an idea of how to do this?

Re: Values form multiple rows to one row

Mariusz — Fri, 01 Nov 2019 10:45:16 GMT

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.

Re: Values form multiple rows to one row

Anonymous — Fri, 01 Nov 2019 10:49:46 GMT

Thanks Mariusz

This is the solution!

Re: Values form multiple rows to one row

Anonymous — Wed, 22 Apr 2020 14:22:03 GMT

Please help how can i do this scenario.

places	AMA00241_mangoes_raw	AMA01234_banana_raw	AMA04545_apples_raw	Type	ID
Texas	Available	Available		Oragnic fruit	A0001
Virginia	Available	Available		Oragnic fruit	A0001
MD	Available		Available	Oragnic fruit	A0001
DC	Available	Available		Oragnic fruit	A0001
VA	Available	Available	Available	Oragnic fruit	A0001
CA	Available	Available		Regular Fruit	B0001
NJ	Available	Available		Regular Fruit	B0001
NY	Available		Available	Regular Fruit	B0001
DE	Available	Available		Regular Fruit	B0001
PA	Available	Available	Available	Regular Fruit	B0001

Expected Results:

AMA00241_mangoes_raw	AMA01234_banana_raw	AMA04545_apples_raw	Type	ID
Available	Available	Available	Oragnic fruit	A0001
Available	Available	Available	Regular Fruit	B0001

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%