Forum Discussion

Prasad23's avatar
Prasad23
Frequent Visitor
2 years ago
Solved

Merge the values into one

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.    
"DAX help"
"DAX help" DAX
'Need help'
  • xifeng_L's avatar
    xifeng_L
    2 years ago

    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~

     

     

mark_endicott's avatar
mark_endicott
Super User
2 years ago

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's avatar
Prasad23
Frequent Visitor
2 years ago

mark_endicott  this is sample data,

BUSub_buDescriptioncreated dateclosed date
ServiceService1_  Service 1&25/10/20245/20/2024
ServiceService1_  dummy5/10/20245/22/2024
ServiceService1_  approc5/11/20245/27/2024
ServiceService2_  need 2 ser5/9/20245/23/2024
Serviceservice3_  seedand plants5/13/20245/24/2024
Serviceservice3_  flowwers5/15/20245/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,

BUSub_bucreated dateclosed dateAgeing
ServiceService1_  5/10/20245/20/202410 Days
ServiceService1_  5/10/20245/22/202412 Days
ServiceService1_  5/11/20245/27/202416 Days
ServiceService2_  5/9/20245/23/202414 Days
Serviceservice3_  5/13/20245/24/202411 Days
Serviceservice3_  5/15/20245/22/20247 Days

 

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

BUSub_bucreated dateclosed dateResult
ServiceService1_  5/10/20245/20/202410-16 days
ServiceService1_  5/10/20245/22/2024
ServiceService1_  5/11/20245/27/2024
ServiceService2_  5/9/20245/23/202414 days
Serviceservice3_  5/13/20245/24/20247-11 days
Serviceservice3_   5/15/20245/22/2024
     

Please suggest in dax.


regards,
Prasad.

  • xifeng_L's avatar
    xifeng_L
    Super User
    2 years ago

    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~