Only one row should show

[UpharAnand]

Hi,

Please review the attached two screenshot and the file.

Screenshot 1.

Here I want to show only 1 row for each source ids which will show status as ACTIVE,Its Subscription Activation date and Last Cancellation date. user have multiple activation and cancellation date but I only want to show latest status = Active and its last cancellation date. I want output as given in screenshot 2. Please scroll down for screenshot 2

Scrrenshot2

I have written a dax function but it is showing me output like in screenshot 1 but I need output as screenshot 2

Please help me for correct dax function.

I am uploading the file, kindly review it

https://drive.google.com/file/d/1DYGnjoSLAa_dJasYNnQU7kSIkqxDS2Mf/view?usp=drivesdk

[Ritaf1983 · Accepted solution]

Hi @UpharAnand 

Your table is currently returning multiple rows because there are still unique rows at the status level and subscription number level.

To get to one row at the sourceid level, you need to aggregate all of these columns.
Please create measures like :

Latest Status =

VAR LatestDate =

    CALCULATE(

        MAX('act_subscription'[subscriptionactivateddate]),

        ALLEXCEPT('act_subscription', 'act_subscription'[sourceid])

    )

RETURN

    CALCULATE(

        MAX('act_subscription'[status]),

        FILTER(

            'act_subscription',

            'act_subscription'[subscriptionactivateddate] = LatestDate

            && 'act_subscription'[sourceid] = MAX('act_subscription'[sourceid])

        )

    )

And :

Latest subscription number =

VAR LatestDate =

    CALCULATE(

        MAX('act_subscription'[subscriptionactivateddate]),

        ALLEXCEPT('act_subscription', 'act_subscription'[sourceid])

    )

RETURN

    CALCULATE(

        MAX('act_subscription'[subscriptionnumber]),

        FILTER(

            'act_subscription',

            'act_subscription'[subscriptionactivateddate] = LatestDate

            && 'act_subscription'[sourceid] = MAX('act_subscription'[sourceid])

        )

    )

Result :

The updated PBIX is attached

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

[Ritaf1983 · Accepted solution]

Hi @UpharAnand 

Your table is currently returning multiple rows because there are still unique rows at the status level and subscription number level.

To get to one row at the sourceid level, you need to aggregate all of these columns.
Please create measures like :

Latest Status =

VAR LatestDate =

    CALCULATE(

        MAX('act_subscription'[subscriptionactivateddate]),

        ALLEXCEPT('act_subscription', 'act_subscription'[sourceid])

    )

RETURN

    CALCULATE(

        MAX('act_subscription'[status]),

        FILTER(

            'act_subscription',

            'act_subscription'[subscriptionactivateddate] = LatestDate

            && 'act_subscription'[sourceid] = MAX('act_subscription'[sourceid])

        )

    )

And :

Latest subscription number =

VAR LatestDate =

    CALCULATE(

        MAX('act_subscription'[subscriptionactivateddate]),

        ALLEXCEPT('act_subscription', 'act_subscription'[sourceid])

    )

RETURN

    CALCULATE(

        MAX('act_subscription'[subscriptionnumber]),

        FILTER(

            'act_subscription',

            'act_subscription'[subscriptionactivateddate] = LatestDate

            && 'act_subscription'[sourceid] = MAX('act_subscription'[sourceid])

        )

    )

Result :

The updated PBIX is attached

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

[danextian]

Hi @UpharAnand ,

but I only want to show latest status = Active and its last cancellation date

What if there isi no active status for a source id?

[UpharAnand]

Sorry for this confusion. if there is no active status then that source id will not be shown in the visual.

You can also make changes as per your logic.

[danextian · Accepted solution]

You seemed to be at the right path already. Since you didn't  need to include non-active status, you can just filter those rows out and another calculation for the lastest subscription number

Latest Subscription = 
VAR MaxSubsDate =
    CALCULATE (
        MAX ( act_subscription[subscriptionactivateddate] ),
        ALLEXCEPT ( act_subscription, act_subscription[sourceid] )
    )
RETURN
    CALCULATE (
        MAX ( act_subscription[subscriptionnumber] ),
        FILTER (
            ALLEXCEPT ( act_subscription, act_subscription[sourceid] ),
            act_subscription[subscriptionactivateddate] = MaxSubsDate
                && act_subscription[status] = "active"
        )
    )