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.