Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Earlier function usage help with table field not measure

Hi Community, 

 

I have used Earlier function with a calculated measure before and it worked.

This time, i have a field "PeriodTCDCRank" which i want to use- but this field is in the table itself not a measure.

 

I am using the same field in the screenshot below- it accepts it with Filter (underlined with Green), but not with Earlier- please suggest what am i doing wrong!

I get the error even if i use quotes (') for Earlier around table name

 

Need Help
  • v-deddai1-msft's avatar
    v-deddai1-msft
    5 years ago

    Hi Anonymous ,

     

    Are you trying to create a calculated column or measure? If it is measure , please use the following measure:

     

     

    FoGByPeriodTCDCRank = CALCULATE(SUM('NewFoGSQL'[Feet on the Ground Total]),FILTER(ALLSELECTED('NewFoGSQL'),'NewFoGSQL'[PeriodTCDCRank]) = MAX('NewFoGSQL'[PeriodTCDCRank])))

     

    If not, please explain more about Now, i am trying to do something similar but using a column in Power Query "PeriodTCDCRank" not column on Report View-

     

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

     

    Best Regards,

    Dedmon Dai

3 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    Anonymous , In filter you use allselected on you table and max in place of earlier

    like

    Filter(Allselected('NewfoGsql') ,'NewfoGsql'[periodcdrank] =max('NewfoGsql'[periodcdrank] ))

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Thanks amitchandak 

      I got an error- can you please check

      Context

      Previously, i used EARLIER with a custom column (TC.Period.DC)- i used this formula- it worked

      This column was calculated by concatenating text fields in Report View- 

      TC.Period.DC = CONCATENATE('NewFoGSQL'[Traveler Country with China/ HK],concatenate([Period],[DestinationCountryName]))
       

      Now, i am trying to do something similar but using a column in Power Query "PeriodTCDCRank" not column on Report View-

      This field PeriodTCDCRank is a also text field created by merging 4 other text fields in the table.

      And that's why my original query and even after trying your suggestion- i got this error shared above.

      Please help!

      • v-deddai1-msft's avatar
        v-deddai1-msft
        Icon for Community Support rankCommunity Support
        5 years ago

        Hi Anonymous ,

         

        Are you trying to create a calculated column or measure? If it is measure , please use the following measure:

         

         

        FoGByPeriodTCDCRank = CALCULATE(SUM('NewFoGSQL'[Feet on the Ground Total]),FILTER(ALLSELECTED('NewFoGSQL'),'NewFoGSQL'[PeriodTCDCRank]) = MAX('NewFoGSQL'[PeriodTCDCRank])))

         

        If not, please explain more about Now, i am trying to do something similar but using a column in Power Query "PeriodTCDCRank" not column on Report View-

         

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

         

        Best Regards,

        Dedmon Dai