Forum Discussion
Solved
Distinct Count Buckets
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
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
2 Replies
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 _countThank you AntonioM for your quick response the measure worked fine 😁
