Forum Discussion

Icon for Helper I rank

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....

Icon for Super User rank

Super User

2 years ago

Sounds really strange, could you paste your table not as an image? So I can copy paste

Icon for Helper I rank

Helper I

2 years ago

i have edited the orignal post please check

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
  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
    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

Related content