Forum Discussion

shashiPaul1570's avatar
shashiPaul1570
Frequent Visitor
4 years ago
Solved

Maximum version of each class in data

 Hi All,    I am working on insurance data where I have columns like Policy which is divided into different classes and each class has different versions. No I need the latest version of each class...
Need Help
Show and Tell
Tips and Tricks
  • v-jianboli-msft's avatar
    v-jianboli-msft
    4 years ago

    Hi shashiPaul1570 ,

     

    Please try:

    Column = 
VAR _a =
    CALCULATE (
        MAX ( 'Table'[No_Version] ),
        FILTER (
            'Table',
            [Policy] = EARLIER ( 'Table'[Policy] )
                && [No_Risk Class Code] = EARLIER ( 'Table'[No_Risk Class Code] )
        )
    )
RETURN
    IF (
        [No_Version] = _a,
        MAXX (
            FILTER (
                'Table',
                [Policy] = EARLIER ( 'Table'[Policy] )
                    && [No_Risk Class Code] = EARLIER ( 'Table'[No_Risk Class Code] )
                    && [No_Version] = _a
                    && [Base Premium] = EARLIER('Table'[Base Premium])
            ),
            [Base Premium]
        ),
        0
    )

    Output:

     

    Best Regards,

    Jianbo Li

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

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

Hi shashiPaul1570 ,

 

If you want a measure, please try:

Measure = 
VAR _a =
    CALCULATE (
        MAX ( 'Table'[No_Version] ),
        FILTER (
            ALL ( 'Table' ),
            [Policy] = MAX ( 'Table'[Policy] )
                && [No_Risk Class Code] = MAX ( 'Table'[No_Risk Class Code] )
        )
    )
RETURN
    SUMX (
        FILTER (
            ALL ( 'Table' ),
            [Policy] = MAX ( 'Table'[Policy] )
                && [No_Risk Class Code] = MAX ( 'Table'[No_Risk Class Code] )
                && [No_Version] = _a
        ),
        [Base Premium]
    )

Output:

If you want a calculated column, please try:

Column = 
VAR _a =
    CALCULATE (
        MAX ( 'Table'[No_Version] ),
        FILTER (
            'Table',
            [Policy] = EARLIER('Table'[Policy] )
                && [No_Risk Class Code] = EARLIER('Table'[No_Risk Class Code] )
        )
    )
RETURN
    SUMX (
        FILTER (
            'Table',
            [Policy] =EARLIER('Table'[Policy] )
                && [No_Risk Class Code] =EARLIER('Table'[No_Risk Class Code] )
                && [No_Version] = _a
        ),
        [Base Premium]
    )

Output:

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

shashiPaul1570's avatar
shashiPaul1570
Frequent Visitor
4 years ago

Hi Jianboli ....... thanks for your reply however I find that we are getting the data for each version and I need to keep the maximum one only. I guess I need to create another column where I can apply condition to show data only if it is high version and else zero