How to pass on a filter table based on all distinct months present to aggregation

[smpa01]

My source data is following - pbix here - https://drive.google.com/file/d/14bhoHJUaiX6Gw8WGmzspoFTOe0tpK9Hk/view?usp=sharing

I have a keyTbl and factTbl as following which contains the details of year-site-service-month combinations.

key contains all possible unique combinations of year-site-service-month

and

fact contains non-unique transactions of year-site-service-month

key filters fact on 1 to many

|                       key                            |
|------|-------|---------|-------|---------------------|
| year | site  | service | month | pk                  |
|------|-------|---------|-------|---------------------|
| 2021 | site1 | serv1   | 1     | 2021-site1-serv1-1  |
| 2021 | site1 | serv1   | 2     | 2021-site1-serv1-2  |
| 2021 | site1 | serv1   | 3     | 2021-site1-serv1-3  |
| 2021 | site1 | serv1   | 4     | 2021-site1-serv1-4  |
| 2021 | site1 | serv1   | 5     | 2021-site1-serv1-5  |
| 2021 | site1 | serv1   | 6     | 2021-site1-serv1-6  |
| 2021 | site1 | serv1   | 7     | 2021-site1-serv1-7  |
| 2021 | site1 | serv1   | 8     | 2021-site1-serv1-8  |
| 2021 | site1 | serv1   | 9     | 2021-site1-serv1-9  |
| 2021 | site1 | serv1   | 10    | 2021-site1-serv1-10 |
| 2021 | site1 | serv1   | 11    | 2021-site1-serv1-11 |
| 2021 | site1 | serv1   | 12    | 2021-site1-serv1-12 |
| 2021 | site1 | serv2   | 1     | 2021-site1-serv2-1  |
| 2021 | site1 | serv2   | 2     | 2021-site1-serv2-2  |
| 2021 | site1 | serv2   | 3     | 2021-site1-serv2-3  |
| 2021 | site1 | serv2   | 4     | 2021-site1-serv2-4  |
| 2021 | site1 | serv2   | 5     | 2021-site1-serv2-5  |
| 2021 | site1 | serv2   | 6     | 2021-site1-serv2-6  |
| 2021 | site1 | serv2   | 7     | 2021-site1-serv2-7  |
| 2021 | site1 | serv2   | 8     | 2021-site1-serv2-8  |
| 2021 | site1 | serv2   | 9     | 2021-site1-serv2-9  |
| 2021 | site1 | serv2   | 10    | 2021-site1-serv2-10 |
| 2021 | site1 | serv2   | 11    | 2021-site1-serv2-11 |
| 2021 | site1 | serv2   | 12    | 2021-site1-serv2-12 |
| 2021 | site2 | serv1   | 1     | 2021-site2-serv1-1  |
| 2021 | site2 | serv1   | 2     | 2021-site2-serv1-2  |
| 2021 | site2 | serv1   | 3     | 2021-site2-serv1-3  |
| 2021 | site2 | serv1   | 4     | 2021-site2-serv1-4  |
| 2021 | site2 | serv1   | 5     | 2021-site2-serv1-5  |
| 2021 | site2 | serv1   | 6     | 2021-site2-serv1-6  |
| 2021 | site2 | serv1   | 7     | 2021-site2-serv1-7  |
| 2021 | site2 | serv1   | 8     | 2021-site2-serv1-8  |
| 2021 | site2 | serv1   | 9     | 2021-site2-serv1-9  |
| 2021 | site2 | serv1   | 10    | 2021-site2-serv1-10 |
| 2021 | site2 | serv1   | 11    | 2021-site2-serv1-11 |
| 2021 | site2 | serv1   | 12    | 2021-site2-serv1-12 |
| 2021 | site2 | serv2   | 1     | 2021-site2-serv2-1  |
| 2021 | site2 | serv2   | 2     | 2021-site2-serv2-2  |
| 2021 | site2 | serv2   | 3     | 2021-site2-serv2-3  |
| 2021 | site2 | serv2   | 4     | 2021-site2-serv2-4  |
| 2021 | site2 | serv2   | 5     | 2021-site2-serv2-5  |
| 2021 | site2 | serv2   | 6     | 2021-site2-serv2-6  |
| 2021 | site2 | serv2   | 7     | 2021-site2-serv2-7  |
| 2021 | site2 | serv2   | 8     | 2021-site2-serv2-8  |
| 2021 | site2 | serv2   | 9     | 2021-site2-serv2-9  |
| 2021 | site2 | serv2   | 10    | 2021-site2-serv2-10 |
| 2021 | site2 | serv2   | 11    | 2021-site2-serv2-11 |
| 2021 | site2 | serv2   | 12    | 2021-site2-serv2-12 |

|                          fact                               |
|------|-------|---------|-------|---------------------|------|
| year | site  | service | month | pk                  | val  |
|------|-------|---------|-------|---------------------|------|
| 2021 | site1 | serv1   | 1     | 2021-site1-serv1-1  | 100  |
| 2021 | site1 | serv1   | 2     | 2021-site1-serv1-2  | 200  |
| 2021 | site1 | serv1   | 3     | 2021-site1-serv1-3  | 300  |
| 2021 | site1 | serv1   | 4     | 2021-site1-serv1-4  | 400  |
| 2021 | site1 | serv1   | 5     | 2021-site1-serv1-5  | 500  |
| 2021 | site1 | serv1   | 6     | 2021-site1-serv1-6  | 600  |
| 2021 | site1 | serv1   | 7     | 2021-site1-serv1-7  | 700  |
| 2021 | site1 | serv1   | 8     | 2021-site1-serv1-8  | 800  |
| 2021 | site1 | serv1   | 9     | 2021-site1-serv1-9  | 900  |
| 2021 | site1 | serv1   | 10    | 2021-site1-serv1-10 | 1000 |
| 2021 | site1 | serv1   | 11    | 2021-site1-serv1-11 | 1100 |
| 2021 | site1 | serv1   | 12    | 2021-site1-serv1-12 | 1200 |
| 2021 | site1 | serv1   | 12    | 2021-site1-serv1-12 | 1300 |
| 2021 | site1 | serv1   | 12    | 2021-site1-serv1-12 | 1400 |
| 2021 | site1 | serv2   | 2     | 2021-site1-serv2-2  | 1500 |
| 2021 | site1 | serv2   | 3     | 2021-site1-serv2-3  | 1600 |
| 2021 | site1 | serv2   | 4     | 2021-site1-serv2-4  | 1700 |
| 2021 | site1 | serv2   | 5     | 2021-site1-serv2-5  | 1800 |
| 2021 | site1 | serv2   | 4     | 2021-site1-serv2-4  | 1900 |
| 2021 | site1 | serv2   | 5     | 2021-site1-serv2-5  | 2000 |
| 2021 | site2 | serv1   | 1     | 2021-site2-serv1-1  | 2100 |
| 2021 | site2 | serv1   | 2     | 2021-site2-serv1-2  | 2200 |
| 2021 | site2 | serv1   | 3     | 2021-site2-serv1-3  | 2300 |
| 2021 | site2 | serv1   | 2     | 2021-site2-serv1-2  | 2400 |
| 2021 | site2 | serv1   | 3     | 2021-site2-serv1-3  | 2500 |
| 2021 | site2 | serv2   | 1     | 2021-site2-serv2-1  | 2600 |
| 2021 | site2 | serv2   | 2     | 2021-site2-serv2-2  | 2700 |
| 2021 | site2 | serv2   | 3     | 2021-site2-serv2-3  | 2800 |
| 2021 | site2 | serv2   | 4     | 2021-site2-serv2-4  | 2900 |
| 2021 | site2 | serv2   | 5     | 2021-site2-serv2-5  | 3000 |
| 2021 | site2 | serv2   | 6     | 2021-site2-serv2-6  | 3100 |
| 2021 | site2 | serv2   | 7     | 2021-site2-serv2-7  | 3200 |
| 2021 | site2 | serv2   | 8     | 2021-site2-serv2-8  | 3300 |
| 2021 | site2 | serv2   | 9     | 2021-site2-serv2-9  | 3400 |
| 2021 | site2 | serv2   | 10    | 2021-site2-serv2-10 | 3500 |
| 2021 | site2 | serv2   | 11    | 2021-site2-serv2-11 | 3600 |
| 2021 | site2 | serv2   | 12    | 2021-site2-serv2-12 | 3700 |
| 2021 | site2 | serv2   | 9     | 2021-site2-serv2-9  | 3800 |
| 2021 | site2 | serv2   | 10    | 2021-site2-serv2-10 | 3900 |
| 2021 | site2 | serv2   | 11    | 2021-site2-serv2-11 | 4000 |
| 2021 | site2 | serv2   | 12    | 2021-site2-serv2-12 | 4100 |

I am building a matrix viz by

bringing site-service-month from key tbl

and showing the sum of fact[val] for which (site-service-month) the total disitnct count of months are 12. So according to that,

only site1-serv1 and site2-serv2 will qualify.

I wrote the following measure but it is only giving me the aggegated value on the subtotatal level where as  I need the aggreagtion to work for site-service-month level too

Measure =
VAR _monthsToCheck = 12
VAR _baseTbl =
    ADDCOLUMNS (
        SUMMARIZE ( 'fact', 'fact'[site], 'fact'[service], 'fact'[month] ),
        "@UniqueMonthCount", CALCULATE ( DISTINCTCOUNT ( 'fact'[month] ), REMOVEFILTERS ( 'fact'[month] ) )
    )
VAR _filtTbl =
    FILTER ( _baseTbl, [@UniqueMonthCount] = _monthsToCheck )
VAR _sum =
    CALCULATE ( SUM ( 'fact'[val] ), _filtTbl )
RETURN
    _sum

Thank you in advance.

[smpa01 · Accepted solution]

Measure2 = 
CALCULATE (
    SUM ( 'fact'[val] ),
    FILTER (
        ADDCOLUMNS (
            'fact',
            "X",
                CALCULATE (
                    DISTINCTCOUNT ( 'fact'[month] ),
                    ALLEXCEPT ( 'fact', 'fact'[site], 'fact'[service] )
                )
        ),
        [X] = 12
    )
)