Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Calculating Max values for each group

Hello,

 

I have a data set similar to the table below that contains the first two columns "ProductName" and "ProductVersion". Currently the third column "LatestVersion" is blank.

How can I calculate the values on the third column so that it I can flag the Max values for each different "ProductName"?

 

Thanks in advance

 

ProductNameProductVersionLatestVersion
Alpha1.22.5No
Alpha2.0.1No
Alpha2.0.15Yes
Bravo21.1.0No
Bravo22.0.3No
Bravo22.1.3No
Bravo22.1.14Yes
Charlie5.13.3Yes
Charlie4.13.3No
Charlie3.13.3No
Need Help
  • Fowmy's avatar
    Fowmy
    2 years ago

    Anonymous 

    Using this as a calculated column in your table:

    Latest Ver = 
IF(
    CALCULATE(
        MAX(Table02[ProductVersion]),
        ALLEXCEPT( Table02 , Table02[ProductName] )
    ) = Table02[ProductVersion],
    "Yes",
    "No"
)
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Anonymous ,

    You can create two calculated columns as below to get it, please find the details in the attachment.

    PVersion = VALUE ( SUBSTITUTE ( [ProductVersion], ".", "" ) )
    LatestVersion =
IF (
    [PVersion]
        = CALCULATE (
            MAX ( [PVersion] ),
            FILTER (
                ALL ( 'Table' ),
                'Table'[ProductName] = EARLIER ( 'Table'[ProductName] )
            )
        ),
    "Yes",
    "No"
)

    Best Regards

2 Replies

  • Fowmy's avatar
    Fowmy
    Icon for Super User rankSuper User
    2 years ago

    Anonymous 

    Using this as a calculated column in your table:

    Latest Ver = 
IF(
    CALCULATE(
        MAX(Table02[ProductVersion]),
        ALLEXCEPT( Table02 , Table02[ProductName] )
    ) = Table02[ProductVersion],
    "Yes",
    "No"
)
  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Anonymous ,

    You can create two calculated columns as below to get it, please find the details in the attachment.

    PVersion = VALUE ( SUBSTITUTE ( [ProductVersion], ".", "" ) )
    LatestVersion =
IF (
    [PVersion]
        = CALCULATE (
            MAX ( [PVersion] ),
            FILTER (
                ALL ( 'Table' ),
                'Table'[ProductName] = EARLIER ( 'Table'[ProductName] )
            )
        ),
    "Yes",
    "No"
)

    Best Regards