Merge the values into one

[Prasad23]

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.

[v-shex-msft]

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

[mark_endicott]

@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. 

[Prasad23]

@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.

[xifeng_L · Accepted solution]

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~