Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Only one row should show

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

 

Need Help
  • Ritaf1983's avatar
    Ritaf1983
    2 years ago

    Hi Anonymous 

    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's avatar
    danextian
    2 years ago

    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"
        )
    )

4 Replies

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

    Hi Anonymous ,

    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?

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      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's avatar
        danextian
        Icon for Super User rankSuper User
        2 years ago

        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"
        )
    )
  • Ritaf1983's avatar
    Ritaf1983
    Icon for Super User rankSuper User
    2 years ago

    Hi Anonymous 

    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.