Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Returning the max string value in a calculated column

Hello, 

 

Could someone help with the below please?

 

In a calculated column, I need to return the max string for a filtered set of values for differnet records. 

 

If I was doing this in SQL I would use something like:

 

 

max(case when Table2.[Task Code] in ('TASK1A1','TASK1A2,'TASK2A1','TASK2A2') then Table2.[Task Code] end)
over
(partition by Table1.[Ref No])

 

This is what my dataset looks like -  I do not want to return information on TASK3A1,TASK4A1.

 

 

I tried using MAXA to return the max string but as documented it only works with integers/dates etc. This is what I've tried so far:

 

evaluate
sumarize
(
'Table1'[Ref No],
"Max Relevant Task Stage",
calculate(maxa('Table2'[Task Code]), filter('Table2', 'Table2'[Task Code] ="TASK1A1" || 'Table2'[Task Code] ="TASK1A2" ||'Table2'[Task Code] ="TASK2A1" || 'Table2'[Task Code] ="TASK2A2" )
)
)

But it just returns '0' for the Desired Result instead of one of the four required task codes listed in the Dax. 

 

 

Could anyone show me an alternative please?

 

Thanks, 

 

pbix

 

Need Help
Show and Tell

1 Reply

  • v-piga-msft's avatar
    v-piga-msft
    Icon for Resident Rockstar rankResident Rockstar
    7 years ago

    Hi Anonymous,

     

    Assuming that you have table like below.

    And your desired output is the calculated column which return the max string value.

     

     

    Please try the formula below.

     

    Column =
VAR temp =
    SUMMARIZE (
        FILTER (
            'Table1',
            'Table1'[Task Code] IN { "TASK1A1", "TASK1A2", "TASK2A1", "TASK2A2" }
        ),
        Table1[Ref No],
        "Max Relevant Task Stage", CALCULATE ( MAX ( Table1[Task Code] ), VALUES ( Table1[Ref No] ) )
    )
RETURN
    MAXX (
        FILTER ( temp, [Ref No] = EARLIER ( Table3[Ref No] ) ),
        [Max Relevant Task Stage]
    )

     

     

    More details, you could refer to the attachment.

     

    Best Regards,

    Cherry