Forum Discussion

bininja's avatar
bininja
Icon for Helper I rankHelper I
7 years ago
Solved

Pick first value for each id based on date

Hi guys,   I am struggling to pick up the first value registered for a id based on the earliest date. So for the customer below, a membership is first created on 01.01.2016 and the first activity ...
Need Help
Tips and Tricks
  • v-juanli-msft's avatar
    v-juanli-msft
    7 years ago

    Hi bininja 

    Create measures

    Measure = DATEDIFF(MAX('Table'[created date]),MAX('Table'[activity date]),DAY)

Measure 2 =
RANKX (
    FILTER (
        ALL ( 'Table' ),
        'Table'[id] = MAX ( 'Table'[id] )
            && 'Table'[created date] = MAX ( 'Table'[created date] )
    ),
    [Measure],
    ,
    ASC,
    DENSE
)
     
    Best Regards
    Maggie
    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Ashish_Mathur's avatar
Ashish_Mathur
Icon for Super User rankSuper User
7 years ago

Hi,

You may download my PBI file from here.

Hope this helps.

  • bininja's avatar
    bininja
    Icon for Helper I rankHelper I
    7 years ago

    Ashish_Mathur can this be done with a measure aswell? I see that you created a calculated column. I only have the possibility to create measures in the dataset as it is a live connection which limits alot of the options for me.

    • Ashish_Mathur's avatar
      Ashish_Mathur
      Icon for Super User rankSuper User
      7 years ago

      Hi,

      I am not sure of how to solve this question with a measure.