Creating Bins from distinct counts

[bolarinwa]

Hi all,

I have my data structure as shown below:

Account ID	case ID	Created date
aab	11	11-10-2018
aac	12	11-10-2018
aad	13	12-10-2018
aae	21	01-12-2017
aab	22	11-10-2018
aad	23	12-10-2018
aae	24	13-10-2018

I'm trying to create bins of intervals based on distinct counts. Basically i want to produce this type of table below based on the original data:

Count of case interval	count of acc id
0-10	5
11-20	4
21-30	2

the above table means that 5 unique acc have distinct count of id between 0-11. I also want to be able to filter this new table by dates in the original data.

Thanks

[v-lili6-msft]

hi, @bolarinwa

After my research,  for calculate table can't be filtered by dates with slicer in the original data, 

you could try this way to create a measure

Step1:

Create a Count of case interval fact table like below

Step2:

Use this formula to create a count of acc id measure

count of acc id = var _table=FILTER(GENERATE('case interval',Table1),Table1[case ID]>='case interval'[start]&&Table1[case ID]<='case interval'[end]) return
CALCULATE(DISTINCTCOUNT(Table1[Account ID]),_table)+0

Result:

and it also can be filtered by dates in the original data.

here is pbix, please try it.

https://www.dropbox.com/s/nrdmg1liy88jtr5/Creating%20Bins%20from%20distinct%20counts.pbix?dl=0

Best Regards,

Lin

[bolarinwa]

Hi @v-lili6-msft,

thanks for your response. I guess you took the case as a value? what i wanted was a distinct count of case id. i.e for interval 0-11 it means the distinct count of case id is less than 2 ( and not the case id itself).

the result should look like this:

count of case id interval	count of acc id
0-10	4
11-20	0
21-30	0
Total	4

 
Thanks

[v-lili6-msft]

hi, @bolarinwa

Is it this measure:

Measure = var _table=FILTER(GENERATE('case interval',Table1),DISTINCTCOUNT(Table1[Account ID])>='case interval'[start]&&DISTINCTCOUNT(Table1[Account ID])<='case interval'[end]) return
CALCULATE(DISTINCTCOUNT(Table1[Account ID]),_table)+0

Result:

By the way, for this measure will due to a measure total problem, here is a post for you refer to

https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

If it is not your case, please share some simple data sample and expected output with the explanation. the data sample you 

have provided is too big.

Thank you in advance for your understanding.

Best Regards,

Lin

[bolarinwa]

Hi @v-lili6-msft, this is a simple format of the data:

AccountId	Case ID	CreatedDate
	5000O00001ZGI4AQ	16-11-2018 11.22
	5000O00001ZGmKRQ	17-11-2018 6.20
	5000O00001ZGYDpQ	16-11-2018 16.20
	5000O00001ZHmvCQ	19-11-2018 8.57
00001j	5000O00001ZGzvCQ	17-11-2018 14.36
00001j	5000O00001ZIJ6WQ	19-11-2018 17.50
00001j	5000O00001ZHpk3Q	19-11-2018 9.41
00001j	5000O00001ZFiLDQ	15-11-2018 14.01
00001j	5000O00001YtMGxQ	13-11-2018 15.01
00001j	5000O00001ZFr1nQ	15-11-2018 17.02
00001j	5000O00001ZFs0zQ	15-11-2018 17.28
00001j	5000O00001ZGXjSQ	16-11-2018 16.09
00001j	5000O00001ZI9oGQ	19-11-2018 14.54
00001j	5000O00001ZGfJjQ	16-11-2018 19.55
00001j	5000O00001ZIoE3Q	20-11-2018 12.09
00001j	5000O00001ZHajMQ	18-11-2018 22.36
00001j	5000O00001ZHZbRQ	18-11-2018 21.33
00001j	5000O00001ZJ48IQ	20-11-2018 17.34
00001j	5000O00001YsWReQ	12-11-2018 12.55
00001j	5000O00001Ytia4Q	14-11-2018 8.33
00001j	5000O00001YtiKBQ	14-11-2018 8.26
00001j	5000O00001Yti6iQ	14-11-2018 8.21
00001j	5000O00001YsJtGQ	12-11-2018 9.18
00001j	5000O00001ZIMECQ	19-11-2018 19.17
00001l	5000O00001YtdA6Q	14-11-2018 3.28
00001l	5000O00001ZG1K8Q	16-11-2018 0.50
00001l	5000O00001ZJbPJQ	21-11-2018 13.59
00001m	5000O00001ZJIAvQ	21-11-2018 7.25
00001m	5000O00001ZHjlCQ	19-11-2018 7.59
00001m	5000O00001YtMgpQ	13-11-2018 15.10
00001m	5000O00001ZIvNPQ	20-11-2018 14.29
00001m	5000O00001ZHlThQ	19-11-2018 8.31
00001m	5000O00001ZIgOfQ	20-11-2018 9.48
00001m	5000O00001ZHgk2Q	19-11-2018 5.50
00001m	5000O00001ZG77UQ	16-11-2018 7.53
00001m	5000O00001ZJ0XDQ	20-11-2018 16.09
00001m	5000O00001ZJUufQ	21-11-2018 11.51
00001m	5000O00001YtSOJQ	13-11-2018 17.22
00001n	5000O00001Yt6J2Q	13-11-2018 9.56
00001n	5000O00001Yt5sBQ	13-11-2018 9.48

my desired output should look like this

Distint Count of Case Interval	Distinct Count of AccountID
1-10	4
11-20	5
21--30	11
>30	8

 This means that only 4 unique accounts have between 1 and 10  unique cases in a selected time frame while 5 accounts had between 11-20 unique cases in the same time frame.

Thanks for your efforts

[v-lili6-msft]

hi, @bolarinwa

Could please share some sample data and corresponding expected output?

You can upload it to OneDrive and post the link here or just post sample data here. Do mask sensitive data before uploading.

Best Regards,

Lin

[bolarinwa]

Hi @v-lili6-msft,

find below the link to the data as requested.

https://www.dropbox.com/sh/32kox34qel77mvc/AACFOJE4AHYw3pjvmD6-pbhUa?dl=0

thanks

[bolarinwa]

Hi, @Lin,

thanks for your response. I guess you took the case as a value? what i wanted was a distinct count of case id. i.e for interval 0-11 it means the distinct count of case id is less than 2 ( and not the case id itself).

Thanks