CODEWITHSUNDEEP
powerbidax
smpa01
smpa01

Reputation: 4346

DAX to aggregate only for values from Lookup Table

My sample source data is following with two tables

key

|           key           |
|-------|---------|-------|
| site  | service | month |
|-------|---------|-------|
| site1 | serv1   | 1     |
| site1 | serv1   | 2     |
| site1 | serv1   | 3     |
| site1 | serv1   | 4     |
| site1 | serv1   | 5     |
| site2 | serv1   | 1     |
| site2 | serv1   | 2     |
| site2 | serv1   | 3     |
| site2 | serv1   | 4     |
| site3 | serv1   | 1     |
| site3 | serv1   | 2     |
| site3 | serv1   | 3     |
| site3 | serv1   | 4     |
| site3 | serv1   | 5     |
| site3 | serv1   | 6     |
| site3 | serv1   | 7     |
| site4 | serv1   | 1     |
| site4 | serv1   | 2     |
| site4 | serv1   | 3     |
| site4 | serv1   | 4     |

and lookup

| Lookup |
|--------|
| site   |
|--------|
| site1  |
| site3  |
| site4  |

I am building a matrix viz by bringing site,service,month from key tbl and counting how many total months are there by each site-service but I only want to show the aggregation for sites that only appear in Lookup.

My desired result is following, i.e. site2 does not appear in lookup and I don't want any aggregation to be returned for that.

|            Result                |
|--------|---------|-------|-------|
| site   | service | month | Count |
|--------|---------|-------|-------|
| site1  | serv1   | 1     | 5     |
| site1  | serv1   | 2     | 5     |
| site1  | serv1   | 3     | 5     |
| site1  | serv1   | 4     | 5     |
| site1  | serv1   | 5     | 5     |
| site3  | serv1   | 1     | 7     |
| site3  | serv1   | 2     | 7     |
| site3  | serv1   | 3     | 7     |
| site3  | serv1   | 4     | 7     |
| site3  | serv1   | 5     | 7     |
| site3  | serv1   | 6     | 7     |
| site3  | serv1   | 7     | 7     |
| site4  | serv1   | 1     | 4     |
| site4  | serv1   | 2     | 4     |
| site4  | serv1   | 3     | 4     |
| site4  | serv1   | 4     | 4     |

I have so far tried with these two measures

countSiteService =
CALCULATE ( COUNT ( 'key'[month] ), REMOVEFILTERS ( 'key'[month] ) )

countSiteServiceLookup =
CALCULATE (
    [countSiteService],
    TREATAS ( VALUES ( Lookup[site] ), 'key'[site] )
)

But they don't give me what I want

Currrent

Both the tables are currently disconnected. But even if I connect them on site, the result does not differ.

How can I achieve the end result with the current model and not by putting any hardcoded filters on the filter pane at all?

Upvotes: 0

Views: 298

Answers (1)

Alexis Olson
Alexis Olson

Reputation: 40244

What you have gives the total 5 + 7 + 4 = 16 since you're overriding the site filter context.

I suspect you want something more like this:

countSiteServiceLookup =
CALCULATE (
    [countSiteService],
    KEEPFILTERS ( 'key'[site] IN VALUES ( Lookup[site] ) )
)

Result:

Screenshot

Upvotes: 1

Related Questions