Having Clause or something close

[adjohnson2]

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. 

[d_gosbell · Accepted solution]

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

[adjohnson2]

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)

[d_gosbell]

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.

[adjohnson2]

No, there shouldn't be any accounts with multiple legal entities. 

[d_gosbell]

---

@adjohnson2 wrote:

No, there shouldn't be any accounts with multiple legal entities. 

---

So shouldn't seems a bit vague - do you mean "in theory this should not happen" or "I've double checked and this is definitely not the case"?

Assuming this is not the case I'm not sure what else could cause the higher total count. I tried mocking up a simple model and I can't make it behave like your screenshot. I'm wondering if there is some bi-directional relationship in your model or some filter on the visual that is causing this. Can you create a test file with some dummy data and reproduce this issue? If so it would help if you could post such a file back to this thread. 

[adjohnson2]

No need to wait until tomorrow, I was too eager to find out. So there are cases where the account number is in multiple legal entities. 

[d_gosbell]

---

@adjohnson2 wrote:

No need to wait until tomorrow, I was too eager to find out. So there are cases where the account number is in multiple legal entities. 

---

So that sounds like it probably explains this issue then and it's either a data quality issue that needs cleaning up or maybe just a fact of life that users need to be trained on when using this metric.

[adjohnson2]

I got the 'Total' row to calculate correctly. It was a user limited knowledge error. There are two account# fields, the one I didn't use in my measure changed as the legal entity changed. So everything is good now.

[adjohnson2]

Please be on tomorrow, I'll have a more definite answer then.

[Greg_Deckler]

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