Forum Discussion

mariaSam1014's avatar
mariaSam1014
Icon for Helper I rankHelper 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
Gabry's avatar
Gabry
Icon for Super User rankSuper 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
Icon for Helper I rankHelper 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
    Icon for Super User rankSuper 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
      Icon for Helper I rankHelper 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.

    • mariaSam1014's avatar
      mariaSam1014
      Icon for Helper I rankHelper I
      2 years ago

      if I may elaborate thsi sir,

      the thing wrong with this in my case is:
      It is counting a same occupierID in all categories in which they appear for e.g:
      11 BAC drive is being counted in EXTREME-DETERIORATING
      as well as 11 BAC Drive is being counted in MEDIUM - IMPORVING
      and also 11 BAC Drive is being counted in  HIGH-DETERIORATING

      whereas (as of my original post) I want 11 BAC drive to be counted in EXTREME - DETERIORATING category ONLY as this is on highest priority.

      • Gabry's avatar
        Gabry
        Icon for Super User rankSuper User
        2 years ago

        Understood sorry, don't know how to do it then