Forum Discussion

mariaSam1014's avatar
mariaSam1014
Helper I
2 years ago

COUNT based on categorized criteria(priority basis)

HI Power community, This is my first post ever:   Tenant OccupierId Location Weekly _RN RISK Severity Level RN_delta Severity Trend ABC 11 BAC drive Bathroom 1 237.33 EXTREME 237....
Need Help
mariaSam1014's avatar
mariaSam1014
Helper I
2 years ago

i have edited the orignal post please check

Gabry's avatar
Gabry
Super User
2 years ago

Ok this is your formula for calculated column / measure

ID Count = CALCULATE(COUNT('Table'[OccupierId]), ALLEXCEPT('Table', 'Table'[Tenant], 'Table'[Severity Trend], 'Table'[OccupierId]))

please mark as accepted solution
  • mariaSam1014's avatar
    mariaSam1014
    Helper I
    2 years ago

    this is counting every instant of occupier ID whereas my requirement is to count the occupier id only once in sequence of severity trend and severity level. please read my full original post sir I have a criteria based count not only count all instances.
    I have already tried dictinct count, count, sumx with count (I need to have a lookup kind of thing which will look up (the nested criteria) or may be COUNTIF but i dont know how should i structure it

    • Gabry's avatar
      Gabry
      Super User
      2 years ago

      Well man if you just need to count the number of occupier id for tenant, risk level and security this is the formula:

      Test =
      CALCULATE(DISTINCTCOUNT('Table'[OccupierId]), ALLEXCEPT('Table', 'Table'[Tenant], 'Table'[Severity Trend],  'Table'[RISK Severity Level]))



       

      as I can see it works perfectly.


      Example 

      Tenant: XYZ

      Risk Level: Medium

      Trend: Deteriorating

       

      NUMBER OF OCCUPIER 3

       

       

      • mariaSam1014's avatar
        mariaSam1014
        Helper I
        2 years ago

        have you seen my required answer table in my orignal post? If you can generate that fro your calculation I would appreciate that
        You are giving me simple count each instance, this is not required at all. Thankyou for this help but exactly this is wrong with what actually is required that its counting each instance correctly. Not required.