Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
mariaSam1014
Helper I
Helper I

COUNT based on categorized criteria(priority basis)

HI Power community,
This is my first post ever:

 

TenantOccupierIdLocationWeekly _RNRISK Severity LevelRN_deltaSeverity Trend
ABC11 BAC driveBathroom 1237.33EXTREME237.33Deteriorating
ABC11 BAC driveKitchen276.86HIGH39.52Deteriorating
ABC11 BAC driveLiving room201.00MEDIUM-19.00Improving
ABC5 PlaceBathroom 1276.86HIGH38.00Deteriorating
ABC5 PlaceKitchen297.29HIGH-17.57Improving
ABC5 PlaceLiving room211.00MEDIUM36.86Steady
ABC892 cloud placeBathroom 1212.00MEDIUM7.00Steady
ABC892 cloud placeKitchen200.43MEDIUM7.00Steady
ABC892 cloud placeLiving room215.14MEDIUM-126.86Improving
XYZ63 Hock AvenueBathroom 1200.43MEDIUM50.14Deteriorating
XYZ63 Hock AvenueKitchen215.14MEDIUM36.86Steady
XYZ63 Hock AvenueLiving room284.29HIGH122.57Deteriorating
XYZ16 cook PlaceBathroom 1276.86HIGH-126.86Improving
XYZ16 cook PlaceKitchen201.00MEDIUM50.14Deteriorating
XYZ16 cook PlaceLiving room237.33EXTREME36.86Steady
XYZ12 GIX driveBathroom 1338.86EXTREME36.86Steady
XYZ12 GIX driveKitchen212.00MEDIUM-17.57Improving
XYZ12 GIX driveLiving room200.43MEDIUM34.57Deteriorating



I have this table visual in my report and the highlighted cloumns are the categories that need to be considered in this post.
Please note actual data has thousand of rows , here I want to count no of occupier that lie in a category(Mentioned below) and once an occupierID is counted in a category, it should not be counted in other category.

EXPLANATION: the severity (highlighted columns) are assigned based on the occupiers so the counter should count like this

Number of occupier for tenant ABC where:
RISK Severity Level = "Extreme" && Severity Trend = "Deteriorating"
RISK Severity Level = "Extreme" && Severity Trend = "Steady"
RISK Severity Level = "Extreme" && Severity Trend = "Improving"

RISK Severity Level = "High" && Severity Trend = "Deteriorating"
RISK Severity Level = "High" && Severity Trend = "Steady"
RISK Severity Level = "High" && Severity Trend = "Improving"

RISK Severity Level = "Medium" && Severity Trend = "Deteriorating "
RISK Severity Level = "Medium" && Severity Trend = "Steady"
RISK Severity Level = "Medium" && Severity Trend = "Improving"

PLEASE NOTE that my requirement is to count the occupierID only Once in this counter and priority of counter is it should count like above mentioned sequence (extreme first , then High then medium{and second catogory also in same sequence as mentioned}...)
OutputS of above table would look like this

Tenant : ABC  
RISK Severity LevelSeverity TrendCOUNT OF OCCUPIER ID
Extreme (80%)Deteriorating1
Extreme (80%)Steady 
Extreme (80%)Improving 
High (75%)Deteriorating1
High (75%)Steady 
High (75%)Improving 
Medium (70%)Deteriorating 
Medium (70%)Steady1
Medium (70%)Improving 

 

Tenant : XYZ  
RISK Severity LevelSeverity TrendCOUNT OF OCCUPIER ID
Extreme (80%)Deteriorating 
Extreme (80%)Steady2
Extreme (80%)Improving 
High (75%)Deteriorating1
High (75%)Steady 
High (75%)Improving 
Medium (70%)Deteriorating 
Medium (70%)Steady 
Medium (70%)Improving 


Please i need the best possible way to achieve this outcome.

THANKYOU

11 REPLIES 11
Gabry
Responsive Resident
Responsive Resident

I think you can try with this

calculate(distintcount (occupierID), allexcept(table, risk severity,severity trend)

let me know

Hey Gabri,
This was the first ever solution  I tried but its giving totally wrong results. (counting everything in every criteria)

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

i have edited the orignal post please check

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

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

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



Gabry_1-1711107902906.png

Gabry_2-1711108178429.png

 

as I can see it works perfectly.


Example 

Tenant: XYZ

Risk Level: Medium

Trend: Deteriorating

 

NUMBER OF OCCUPIER 3

 

 

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.

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

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

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.

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.