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