Forum Discussion

bolarinwa's avatar
bolarinwa
Frequent Visitor
7 years ago

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 intervalcount of acc id
0-104
11-200
21-300
Total4


 
Thanks

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