Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
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
@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.
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
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?
@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)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |