Distinct Count Buckets

[cottrera]

Hi 

I have a facts table that has the following columns - Property Ref, Score and Measure. Each property ref has exactly 8 measures each with scores. 

Property Ref	SCORE	Measure
18	0	Arrears
18	0	Damp & Mould
18	0	Damp related health issues
18	0	Guttering
18	0	Pest Control
18	0	Plumbing
18	50	Job Volume
18	100	Vulnerable
185133	0	Guttering
185133	0	Pest Control
185133	25	Plumbing
185133	75	Damp & Mould
185133	50	Arrears
185133	25	Damp related health issues
185133	25	Job Volume
185133	0	Vulnerable
284315	0	Damp & Mould
284315	0	Damp related health issues
284315	0	Guttering
284315	0	Pest Control
284315	0	Plumbing
284315	100	Arrears
284315	100	Job Volume
284315	100	Vulnerable
208	50	Arrears
208	100	Damp & Mould
208	100	Damp related health issues
208	100	Pest Control
208	100	Plumbing
208	100	Guttering
208	100	Job Volume
208	100	Vulnerable

I also have a buckets table with no relationship to the facts table

Order	From	To	Category
1	400	650	CAT 4
2	300	385	CAT 3
3	200	285	CAT 2
4	0	185	CAT 1

I would Like to create a DAX function SUMS the total score per property ref and then counts the distinct property ref that have a total score that matches between the From and To value of my bucket table.

For example, i would exspect the see a result similar to this..

Category	Distinct Property Count
CAT 1	6,403
CAT 2	2,984
CAT 3	1,363
CAT 4	121

thank you Richard

[cottrera]

Thank you AntonioM for your quick response the measure worked fine 😁

[AntonioM · Accepted solution]

Hi, 

I think this should work for you

Distinct Property Count =
var _scores = SUMMARIZE(Fact_Table,Fact_Table[Property Ref], "Score", SUM(Fact_Table[SCORE]))
var _count = SUMX(_scores, IF([Score] <= SELECTEDVALUE(Buckets_Table[To]) && [Score] >= SELECTEDVALUE(Buckets_Table[From]),1))
return _count