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.