Forum Discussion

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