Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
GunnerJ
Post Patron
Post Patron

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.

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

@GunnerJ , Try a new measure like

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

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.