Forum Discussion

v-lili6-msft's avatar
v-lili6-msft
Community Support
7 years ago

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's avatar
bolarinwa
Frequent Visitor
7 years ago

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

  

 

AccountIdCase IDCreatedDate
 5000O00001ZGI4AQ16-11-2018 11.22
 5000O00001ZGmKRQ17-11-2018 6.20
 5000O00001ZGYDpQ16-11-2018 16.20
 5000O00001ZHmvCQ19-11-2018 8.57
00001j5000O00001ZGzvCQ17-11-2018 14.36
00001j5000O00001ZIJ6WQ19-11-2018 17.50
00001j5000O00001ZHpk3Q19-11-2018 9.41
00001j5000O00001ZFiLDQ15-11-2018 14.01
00001j5000O00001YtMGxQ13-11-2018 15.01
00001j5000O00001ZFr1nQ15-11-2018 17.02
00001j5000O00001ZFs0zQ15-11-2018 17.28
00001j5000O00001ZGXjSQ16-11-2018 16.09
00001j5000O00001ZI9oGQ19-11-2018 14.54
00001j5000O00001ZGfJjQ16-11-2018 19.55
00001j5000O00001ZIoE3Q20-11-2018 12.09
00001j5000O00001ZHajMQ18-11-2018 22.36
00001j5000O00001ZHZbRQ18-11-2018 21.33
00001j5000O00001ZJ48IQ20-11-2018 17.34
00001j5000O00001YsWReQ12-11-2018 12.55
00001j5000O00001Ytia4Q14-11-2018 8.33
00001j5000O00001YtiKBQ14-11-2018 8.26
00001j5000O00001Yti6iQ14-11-2018 8.21
00001j5000O00001YsJtGQ12-11-2018 9.18
00001j5000O00001ZIMECQ19-11-2018 19.17
00001l5000O00001YtdA6Q14-11-2018 3.28
00001l5000O00001ZG1K8Q16-11-2018 0.50
00001l5000O00001ZJbPJQ21-11-2018 13.59
00001m5000O00001ZJIAvQ21-11-2018 7.25
00001m5000O00001ZHjlCQ19-11-2018 7.59
00001m5000O00001YtMgpQ13-11-2018 15.10
00001m5000O00001ZIvNPQ20-11-2018 14.29
00001m5000O00001ZHlThQ19-11-2018 8.31
00001m5000O00001ZIgOfQ20-11-2018 9.48
00001m5000O00001ZHgk2Q19-11-2018 5.50
00001m5000O00001ZG77UQ16-11-2018 7.53
00001m5000O00001ZJ0XDQ20-11-2018 16.09
00001m5000O00001ZJUufQ21-11-2018 11.51
00001m5000O00001YtSOJQ13-11-2018 17.22
00001n5000O00001Yt6J2Q13-11-2018 9.56
00001n5000O00001Yt5sBQ13-11-2018 9.48

 

my desired output should look like this

Distint Count of Case IntervalDistinct Count of AccountID
1-104
11-205
21--3011
>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