Forum Discussion

Chandrashekar's avatar
Chandrashekar
Icon for Resolver III rankResolver III
3 years ago
Solved

Occurrence : Column formula help

Hello Team,   Can you help me to get column formula for occurrence as mentioned in attachment. PBIX: Sample file Excel Formula: =MAX(IF(B2:B6=B2,ROW(B2:B6)-ROW(INDEX(B2:B6,1,1))+1))   Reg...
  • Chandrashekar's avatar
    Chandrashekar
    3 years ago

    Hello,

     

    got the answer.

    Occurence=
    COUNTX(FILTER ( Sheet1, EARLIER ( Sheet1[Number]) = Sheet1[Number] && EARLIER(Sheet1[Month])<=[Month]),
        Sheet1[Number])
     
    Regards,
    Chandrashekar B
Chandrashekar's avatar
Chandrashekar
Icon for Resolver III rankResolver III
3 years ago

Hello,

 

Please find updated link for sample report.

 

PBIX: Sample report 

 

Regards,

Chandrashekar B

 

lbendlin's avatar
lbendlin
Icon for Super User rankSuper User
3 years ago
Occurences = 
var m = SELECTEDVALUE(Sheet1[Month])
var n = SELECTEDVALUE(Sheet1[Number])
return countrows(Filter(ALLSELECTED(Sheet1),Sheet1[Number]=n && Sheet1[Month]>=m))

 

  • Chandrashekar's avatar
    Chandrashekar
    Icon for Resolver III rankResolver III
    3 years ago

    Hello, Can I get column formula instead of DAX.

     

    Regards,

    Chandrashekar B

    • lbendlin's avatar
      lbendlin
      Icon for Super User rankSuper User
      3 years ago
      Occurences = countrows(Filter(ALL(Sheet1),Sheet1[Number]=[Number] && Sheet1[Month]>=[Month]))
      • Chandrashekar's avatar
        Chandrashekar
        Icon for Resolver III rankResolver III
        3 years ago

        Hello,

         

        am getting same numbers.

        Regards,

        Chandrashekar B