cancel
Showing results for
Did you mean:

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.

Frequent Visitor

## Creating Bins from distinct counts

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

7 REPLIES 7
Community Support

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

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Frequent Visitor

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

Community Support

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.

Best Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Frequent Visitor

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.

Community Support

hi, @bolarinwa

Could please share some sample data and corresponding expected output?

Best Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Frequent Visitor

find below the link to the data as requested.

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

thanks

Frequent Visitor

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

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors