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

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

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

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

    no problem, i just cant structure it i am not sure if NESTED IFs work here or COUNTIF