Forum Discussion
Having Clause or something close
I would like for my measure to only count the accounts that have more than n case.
This is what I've tried so far.
Qualified Customer =
CALCULATE(COUNT('Customer Info'[ACCOUNT#]), FILTER('Customer Count', COUNT('Customer Info'[CASE]) > 6))
This pretty much ignores my FILTER() and does a normal Count() of the accounts.
I've also tried
Qualified_Customers =
var Qualified_Customers = CALCULATETABLE(VALUES('Customer Info'[ACCOUNT_NBR]), FILTER('Customer Info', COUNT('Customer Info'[CASE_ID]) > 6))
return CALCULATE(COUNT('Customer Info'[ACCOUNT_NBR]), 'Customer Info'[ACCOUNT_NBR] in Qualified_Customers)
This gives me an error message saying it ran out of avaiable memory or does a normal Count() of the accounts.
Last I tried
Qualified_Customer =
var numberSelect = 6
VAR CEMIn_Customers =
CALCULATETABLE (
VALUES ( 'Customer Info'[ACCOUNT#] ),
ALLSELECTED ( Outages ),
VALUES ( 'Customer Info'[ACCOUNT#] ),
FILTER( 'Customer Info', COUNT('Customer Info'[CASE_ID]) >6)
)
var custTotal = CALCULATE (
COUNT ( 'Customer Info'[ACCOUNT#] ),
ALLSELECTED ( 'Customer Info' ),
'Customer Info'[ACCOUNT#] IN CEMIn_Customers)
RETURN
CALCULATE (
CALCULATE(COUNT('Customer Info'[ACCOUNT#])),
FILTER('Customer Info', custTotal >= numberSelect)
)
This gives me an error message saying it ran out of avaiable memory.
I'm all out of ideas, any help would be greatly appreciated.
So you should be able to do this with a measure like the following
Qualified Customer = COUNTROWS( FILTER( VALUES('Customer Info'[Account_NBR]), -- gets a distinct list of account_nbr CALCULATE( -- forces a context transition so that Case_ID is -- filtered for just those under the current Account_nbr COUNT('Customer Info'[CASE_ID]) ) > 6 ) )If you had a measure that counted case_ids
Case Count = COUNT('Customer Info'[CASE_ID])Then you could simplify this to remove the call to calculate (as measures are wrapped in an implied calculate )
Qualified Customer = COUNTROWS( FILTER( VALUES('Customer Info'[Account_NBR]), -- gets a distinct list of account_nbr [Case Count] > 6 ) )
10 Replies
What does your source data look like? Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
So you should be able to do this with a measure like the following
Qualified Customer = COUNTROWS( FILTER( VALUES('Customer Info'[Account_NBR]), -- gets a distinct list of account_nbr CALCULATE( -- forces a context transition so that Case_ID is -- filtered for just those under the current Account_nbr COUNT('Customer Info'[CASE_ID]) ) > 6 ) )If you had a measure that counted case_ids
Case Count = COUNT('Customer Info'[CASE_ID])Then you could simplify this to remove the call to calculate (as measures are wrapped in an implied calculate )
Qualified Customer = COUNTROWS( FILTER( VALUES('Customer Info'[Account_NBR]), -- gets a distinct list of account_nbr [Case Count] > 6 ) )I do have one more question. I created the DAX below and got the same outcome as you. However, I like how much more simple yours is. Both of our DAX formulas are coming up with incorrect total rows.
Qualified Customer = var CEMIn = SELECTEDVALUE(CEMI_Threshold[CEMIn], 6) var cust_table = GROUPBY( FILTER ( SUMMARIZE ( 'Customer Info', 'Customer Info'[Account_NBR], "Count records", COUNTX('Customer Info', COUNT('Customer Info'[CASE_ID])) ), [Count records] >= CEMIn ), 'Customer Info'[ACCOUNT_NBR]) return CALCULATE(DISTINCTCOUNT('Customer Info'[ACCOUNT_NBR]), 'Customer Info'[ACCOUNT_NBR] in cust_table)Do you have any accounts that have cases with different legal entities? For example if an account had 4 cases with legal entity 1 and 4 with legal entity 2 they will fall under the threashold when split by legal entity, but at the total level they will have 8 cases so will qualify there.
