Forum Discussion
Having Clause or something close
- 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 ) )
No, there shouldn't be any accounts with multiple legal entities.
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.
- Anonymous6 years agoNot applicable
Please be on tomorrow, I'll have a more definite answer then.
- Anonymous6 years agoNot applicable
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_gosbell6 years agoSuper User
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.
- Anonymous6 years agoNot applicable
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.