cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
GunnerJ
Helper IV
Helper IV

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

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 

1 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.

2.PNG

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

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.

View solution in original post

7 REPLIES 7
v-jayw-msft
Community Support
Community Support

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))

1.PNG 

 

Best Regards,

Jay

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.

@v-jayw-msft 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?

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.

2.PNG

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

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.

View solution in original post

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
Super User IV
Super User IV

@GunnerJ , Try a new measure like

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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

@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

@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)



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

March Update

Check it Out!

Click here to read more about the March 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.