Nested Created Measure: Distinctcount values of one table and match within ranges of another

Hi Anonymous 

I build two sample tables as below to have a test.

Claim Data Table:

Claim Buckets Table:

Firstly, I calculate the total Plan Payment Amount by measure.

Total Plan Payment Amount = CALCULATE(SUM('Claim Data'[Plan Payment Amount]]]),FILTER(ALLSELECTED('Claim Data'),'Claim Data'[Claim Serial Number]=MAX('Claim Data'[Claim Serial Number])))

Result:

Then I use two measure to count the number of Claim Serial Number which is in the Bucket Range.

M_Claim Range = CALCULATE(MAX('Claim Buckets'[Claim Range]),FILTER('Claim Buckets',[Total Plan Payment Amount]>='Claim Buckets'[Claim Min]&&[Total Plan Payment Amount]<'Claim Buckets'[Claim Max]))

Count = CALCULATE(DISTINCTCOUNT('Claim Data'[Claim Serial Number]),FILTER('Claim Data',[M_Claim Range]=MAX('Claim Buckets'[Claim Range])))

Result:

You can download the pbix file form this link:

https://qiuyunus-my.sharepoint.com/:u:/g/personal/tongzhou_qiuyunus_onmicrosoft_com/EaE7bxDAfR5DpVrEw_a21GcBeTB0zxuLNmensU4nXUMVyg?e=QsyTVL

Best Regards,

Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.