Forum Discussion

ishanjain's avatar
ishanjain
Frequent Visitor
3 years ago
Solved

Rankx & earlier

I'm trying to build a calculated column called days_counter using rankx to find out how long the column tcc_sum was negative (in order of snapshot timestamp). To my knowledge, I'm using the co...
  • v-yadongf-msft's avatar
    v-yadongf-msft
    3 years ago

    Hi ishanjain ,

     

    Add an index column in Power Query or in your data source.

     

    Create two columns:

    Appear continuously = CALCULATE(MAX('Table'[Index])+1,ALL('Table'),'Table'[defc_neg_pos_] <> EARLIER('Table'[defc_neg_pos_]),'Table'[Index] <= EARLIER('Table'[Index]))

Rank = RANKX(FILTER('Table','Table'[SERVICE_AREA] = EARLIER('Table'[SERVICE_AREA]) && 'Table'[Appear continuously] = EARLIER('Table'[Appear continuously])),'Table'[Snapshot Timestamp],,ASC,Dense)

     

    I think this is the result you want:

     

    Best regards,

    Yadong Fang

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

v-yadongf-msft's avatar
v-yadongf-msft
Icon for Community Support rankCommunity Support
3 years ago

Hi ishanjain ,

 

Add an index column in Power Query or in your data source.

 

Create two columns:

Appear continuously = CALCULATE(MAX('Table'[Index])+1,ALL('Table'),'Table'[defc_neg_pos_] <> EARLIER('Table'[defc_neg_pos_]),'Table'[Index] <= EARLIER('Table'[Index]))

Rank = RANKX(FILTER('Table','Table'[SERVICE_AREA] = EARLIER('Table'[SERVICE_AREA]) && 'Table'[Appear continuously] = EARLIER('Table'[Appear continuously])),'Table'[Snapshot Timestamp],,ASC,Dense)

 

I think this is the result you want:

 

Best regards,

Yadong Fang

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

ishanjain's avatar
ishanjain
Frequent Visitor
3 years ago

v-yadongf-msft I figured it out! I had to change ALL(table) to ALLEXCEPT(Table[service area]) in "Appear continously because my table had a lot of areas and it was not working dynamically for one single area and that worked. Thanks a ton, you saved me 🙂