topic Re: Merge the values into one in DAX Commands and Tips

Merge the values into one

Prasad23 — Thu, 30 May 2024 07:52:52 GMT

Hi Team,

I'm facing the below issue,

I need required result but i'm getting Age(my result).

Could you please help me to get this.

Regards,

Prasad.

Re: Merge the values into one

mark_endicott — Thu, 30 May 2024 08:39:27 GMT

Prasad23 - If your result column is after you have attempted to create a column or measure, it would be helpful if you gave a sample of the base data so we can test a solution on it.

Regardless, I would say this is a task best undertaken in Power Query, rather than DAX. My approach would be to Pivot your Age column, so that for Service 1 you have two columns, instead of two rows. You can then combine the two columns and remove the originals after.

If you need to strip out the word days from one of the columns this will be possible too, and all with using the Power Query UI - no need to write any code.

If you can supply some sample data in the exact format, I can provide you with some steps.

Re: Merge the values into one

Prasad23 — Thu, 30 May 2024 09:23:39 GMT

mark_endicott this is sample data,

BU	Sub_bu	Description	created date	closed date
Service	Service1_	Service 1&2	5/10/2024	5/20/2024
Service	Service1_	dummy	5/10/2024	5/22/2024
Service	Service1_	approc	5/11/2024	5/27/2024
Service	Service2_	need 2 ser	5/9/2024	5/23/2024
Service	service3_	seedand plants	5/13/2024	5/24/2024
Service	service3_	flowwers	5/15/2024	5/22/2024

we need to calculate date difference between Created and Closed date.

i used the
ageing = var a = datediff(Closed date,Created date,day)

return a&" days"

I got like this,

BU	Sub_bu	created date	closed date	Ageing
Service	Service1_	5/10/2024	5/20/2024	10 Days
Service	Service1_	5/10/2024	5/22/2024	12 Days
Service	Service1_	5/11/2024	5/27/2024	16 Days
Service	Service2_	5/9/2024	5/23/2024	14 Days
Service	service3_	5/13/2024	5/24/2024	11 Days
Service	service3_	5/15/2024	5/22/2024	7 Days

but i required , for su_bu service1 the ageing should be 10-16 days.....

BU	Sub_bu	created date	closed date	Result
Service	Service1_	5/10/2024	5/20/2024	10-16 days
Service	Service1_	5/10/2024	5/22/2024
Service	Service1_	5/11/2024	5/27/2024
Service	Service2_	5/9/2024	5/23/2024	14 days
Service	service3_	5/13/2024	5/24/2024	7-11 days
Service	service3_	5/15/2024	5/22/2024

Please suggest in dax.

regards,
Prasad.

Re: Merge the values into one

xifeng_L — Thu, 30 May 2024 16:23:12 GMT

Hi Prasad23 ,

You can try below calculated column expression.

Result 1 = VAR TempTable = CALCULATETABLE( ADDCOLUMNS('Table',"diff",DATEDIFF('Table'[created date],'Table'[closed date],DAY)), ALLEXCEPT('Table','Table'[BU],'Table'[Sub_bu]) ) VAR MinDiff = MINX(TempTable,[diff]) VAR MaxDiff = MAXX(TempTable,[diff]) RETURN IF(MinDiff<>MaxDiff,MinDiff&" - "&MaxDiff&" days",MinDiff&" days")

Or is another result what you want?

Result 2 = VAR TempTable = CALCULATETABLE( ADDCOLUMNS('Table',"diff",DATEDIFF('Table'[created date],'Table'[closed date],DAY)), ALLEXCEPT('Table','Table'[BU],'Table'[Sub_bu]) ) VAR MinDiff = MINX(TempTable,[diff]) VAR MaxDiff = MAXX(TempTable,[diff]) VAR Result = IF(MinDiff<>MaxDiff,MinDiff&" - "&MaxDiff&" days",MinDiff&" days") RETURN IF( ROWNUMBER( ALL('Table'[BU],'Table'[Sub_bu],'Table'[created date],'Table'[closed date]), ORDERBY('Table'[created date],ASC,'Table'[closed date],ASC), PARTITIONBY('Table'[BU],'Table'[Sub_bu]) )=1, Result )

Demo - Merge the values into one.pbix

Did I answer your question? If yes, pls mark my post as a solution and appreciate your Kudos !

Thank you~

Re: Merge the values into one

Anonymous — Mon, 03 Jun 2024 05:19:38 GMT

Hi Prasad23 ,

Did the above suggestions help with your scenario? if that is the case, you can consider Kudo or Accept the helpful suggestions to help others who faced similar requirements.

If these also don't help, please share more detailed information and description to help us clarify your scenario to test.

How to Get Your Question Answered Quickly

Regards,

Xiaoxin Sheng