Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Find latest value (text + decimals) by date/timestamp

Hi! I have a bunch of records (events) coming from the Firebase. Each contains a device operation system version (e.g. Android 9, Android 11). So, I need to create a measure to show the latest OS version for each user. Trying to simply maximize one gives an incorrect result, 'cos "Android 9" > "Android 10". I also tried to handle this via the LASTNOBLANK command, but it doesn't work.

 

I could use the separate table with the information needed (via SQL), but it's crucial to maintain the report flexible with least aggregated data

 

Best regards

Need Help

7 Replies

  • jppv20's avatar
    jppv20
    Icon for Solution Sage rankSolution Sage
    4 years ago

    Hi Anonymous ,

     

    An option could be to create a calculated column with only the number for each version. In that case 10 > 9. You can then create a formula based on the max of this column.

     

    Hope this helps!

     

    Jori

     

    If I answered your question, please mark it as a solution to help other members find it more quickly.

    Connect on Linkedin

     

  • jppv20's avatar
    jppv20
    Icon for Solution Sage rankSolution Sage
    4 years ago

    Anonymous Could you provide some example data?

      • v-easonf-msft's avatar
        v-easonf-msft
        Icon for Community Support rankCommunity Support
        4 years ago

        Hi, Anonymous 

        Try the following measures:

        os_num = 
VALUE ( MID ( MAX ( 'Table'[os] ), 9, 3 ) )
        Max(os) = 
CALCULATE (
    MAX ( 'Table'[os] ),
    FILTER (
        'Table',
        'Table'[name] = MAX ( 'Table'[name] )
            && 'Table'[device] = MAX ( 'Table'[device] )
            && [os_num]
                = MAXX ( ALLEXCEPT ( 'Table', 'Table'[name], 'Table'[device] ), [os_num] )
    )
)

        Best Regards,
        Community Support Team _ Eason
        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • speedramps's avatar
    speedramps
    Icon for Super User rankSuper User
    4 years ago

    Hi VKorikhin

     

    Please conisder this solution and smash the thumbs up button.

     

    In Power Query ...

     

    • right click on the "Query OS version" field > By Non Digit to Digit  all call the column "OS"

    • right click on the "Query OS version" field > By Digit to Digit  all call the column "Version"

    • Change the "Version" data typoe to numeric

    The rest should be straight forward