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      
    )
)
d_gosbell's avatar
d_gosbell
Super User
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

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

    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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

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

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