Forum Discussion

Bpark1994's avatar
Bpark1994
Helper I
8 years ago
Solved

Need Help with Dax (Display Occurrence Number In Calculated Column)

Hello,   Say i have a table with 2 columns and dates out of order like so   Date Color 1/1/2017 blue 2/5/2017 blue 1/24/2017 blue 6/25/2017 red 9/1/2017 red 4/25/2017 ...
  • TomMartens's avatar
    TomMartens
    8 years ago

    Hey,

     

    the DAX statement to create the calculated column looks like this:

    Rank by Date in group color = 
RANKX (
    FILTER (
        'Table2'
        ,'Table2'[Color] = EARLIER ( Table2[Color] )
    )
    ,'Table2'[Date]
    ,
    ,ASC
    ,DENSE
)

    If creating a calculated column you have to be aware that calculated value will not change if for example you apply a filter to the rowset, meaning: if you select May (eg by using a slicer), the value of the column will still show 4 for the color blue on the 26th of May 2017. even if there is now just one row.

     

    Hopefully this is what you are looking for

     

    Regards

    Tom

TomMartens's avatar
TomMartens
Super User
8 years ago

Hey,

 

the DAX statement to create the calculated column looks like this:

Rank by Date in group color = 
RANKX (
    FILTER (
        'Table2'
        ,'Table2'[Color] = EARLIER ( Table2[Color] )
    )
    ,'Table2'[Date]
    ,
    ,ASC
    ,DENSE
)

If creating a calculated column you have to be aware that calculated value will not change if for example you apply a filter to the rowset, meaning: if you select May (eg by using a slicer), the value of the column will still show 4 for the color blue on the 26th of May 2017. even if there is now just one row.

 

Hopefully this is what you are looking for

 

Regards

Tom

  • Bpark1994's avatar
    Bpark1994
    Helper I
    8 years ago

    Perfect! Thank you Tom!