Forum Discussion

bolarinwa's avatar
bolarinwa
Frequent Visitor
7 years ago

Creating Bins from distinct counts

Hi all,

I have my data structure as shown below:

 

Account IDcase IDCreated date
aab1111-10-2018
aac1211-10-2018
aad1312-10-2018
aae2101-12-2017
aab2211-10-2018
aad2312-10-2018
aae2413-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 intervalcount of acc id
0-105
11-204
21-302

 

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

General Comment
Interesting Links
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests

7 Replies

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

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

      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

    • 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

        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