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
Icon for Super User rankSuper 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
      Icon for Super User rankSuper 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~