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~

     

     

4 Replies

  • 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~

         

         

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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