Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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('Cust...
Need Help
  • d_gosbell's avatar
    d_gosbell
    6 years ago

    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      
    )
)
Anonymous's avatar
Anonymous
Not applicable
6 years ago

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's avatar
d_gosbell
Super User
6 years ago
Anonymous 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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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.