Count Accounts without specific statuses when each account can have multiple statuses

[GunnerJ]

Sorry if the title seemed confusing and hopefully I can make things clear here.

I'm needing to get a distinct count of BI_ACCT where the account # has no lines where the BI_SRV_STAT_CD is '1' or '18'. Each account # can have multiple BI_TYPE_SRV which results in multiple statuses per account. 

In the example below I would like to see a count of '2' since account numbers 20 and 40 have no instances where the "BI_SRV_STAT_CD" is '1' or '18'. Even though account# 10 has a status of 21 on row two I don't want it included since on row one is does contain a status of '1'. 

BI_ACCT	BI_SRV_STAT_CD	BI_TYPE_SRV
10	1	INT
10	21	PH
20	21	INT
20	48	TV
30	18	TV
40	21	INT

It's just one table of these three columns. It seems pretty straight forward but I'm not sure how to segment the account #s to then see if any of those rows contains specific criteria. Any help is appreciated.

If I've failed to mention anything please let me know.

Link to sample copy.

https://www.dropbox.com/s/r7byj0r6i31qrbp/Inactives.pbix?dl=0 

[Anonymous]

Hi @GunnerJ ,

Check the measures.

_flag = IF(SELECTEDVALUE('Attrition Rate Go'[BI_SRV_STAT_CD]) in {"1","18"},1,0)

Measure = MAXX(ALLEXCEPT('Attrition Rate Go','Attrition Rate Go'[BI_ACCT]),[_flag])

_count = COUNTROWS(FILTER(DISTINCT('Attrition Rate Go'[BI_ACCT]),[Measure]=1))

 

Best Regards,

Jay

[GunnerJ]

@Anonymous thank you for the reply. The issue with the solution you provided is that account numbers can have multiple services and statuses for those services. 

For example BI_ACCT '107494002' has three different services and two show a status of '1' and the last shows a status of '21'. The flag marks the first two but not the last. When the '0s' are counted I don't want this account included but because one service wasn't flagged its being counted. 

I basically need to have all rows of an account # flagged if ANY row of that # has a status of 1 or 18. 

Is that possible?

[Anonymous · Accepted solution]

Hi @GunnerJ ,

So the second measure is to mark the BI_ACCT if the there's "1" or "18" for that BI_ACCT.

As you can see in the picture.

And here we need to modify the third measure to "[measure] =0".

_count = COUNTROWS(FILTER(DISTINCT('Attrition Rate Go'[BI_ACCT]),[Measure]=0))

Best Regards,

Jay

[GunnerJ]

Thank you! So sorry to have missed that simple edit. Just for learning purposes can you explain the dax that has "Measure" mark all of the same account #s one way or another?

[amitchandak]

@GunnerJ , Try a new measure like

calculate(distinctCOUNT(Table[BI_ACCT]), filter(table, Table[BI_SRV_STAT_CD] in {1,18}))

[GunnerJ]

@amitchandak thank you for the reply. Unfortunantly that measure doesn't appear to meet my goal. 

It's counting all of the rows with a statuses of '1' or '18'. 

I'm needing to look at all rows of specific account numbers and to count them only if NONE of the rows have a status of '1' or '18'. 

In my first post BI_ACCT 10 has two rows. I'd need to look at both rows at the same time and see that one of them has BI_SRV_STAT_CD of '1' and not count it. 

I'd then move onto BI_ACCT 20 and see that NEITHER row has a BI_SRV_STAT_CD of '1' or '18' and therefore add it to the count.

Does this help clear up my issue?

Thank you

[amitchandak]

@GunnerJ , Try like 

measure =
var _tab = Summarize(filter(table, Table[BI_SRV_STAT_CD] in {1,18}),Table[BI_ACCT])
return
calculate(Countrows(Table), filter(Table, Table[BI_ACCT] in _tab)

or

measure =
var _tab = Summarize(filter(table, Table[BI_SRV_STAT_CD] in {1,18}),Table[BI_ACCT])
return
calculate(Countrows(Table), filter(all(Table), Table[BI_ACCT] in _tab)