Need a measure that's a subset of another measure

[GertW]

I have a measure that calculates totals from journal entries. It contains a couple of filters, and it works fine.

Now I want to create another measure that only pulls a subset of the "totals" measure I just described. In other words, I want to keep the calculation and the filter from the first measure, and then, the second measure should only apply a few more filters.

The solution I came up with doesn't work, it seems to ignore the initital filters, and I can't even break down the results by year - if I try, I see the overall total for every single year.

Here's my initial measure:

TOTAL_ACTUAL:=
CALCULATE (
SUM ( fact_SAP_JournalEntries[PRIMARY_AMOUNT] ) * ( -1 ),
FILTER (
fact_SAP_JournalEntries,
RELATED ( 'dim_FCCs'[FOUR_POINT_DESCRIPTION] ) <> "RECLASS"
),
FILTER ( fact_SAP_JournalEntries, fact_SAP_JournalEntries[Remapped] = "No" ),
FILTER (
fact_SAP_JournalEntries,
fact_SAP_JournalEntries[TTH_CENTER] <> "737000100"
&& fact_SAP_JournalEntries[TTH_CENTER] <> "737999994"
&& fact_SAP_JournalEntries[TTH_CENTER] <> "737999999"
))

And here's the second measure, which should be the same as the one above, just a bit more restrictive:

SubsetValue:=
(
CALCULATE (
[TOTAL_ACTUAL],
FILTER (
fact_SAP_JournalEntries,
RELATED ( dim_FCCs[FCC] ) = "737103001"
&& (
RELATED ( dim_Accounts[ACCOUNT] ) = "21601000"
|| RELATED ( dim_Accounts[ACCOUNT] ) = "21601003"))))

I could really need a hand here...

[Anonymous · Accepted solution]

First, please watch something about THE PROPER MODEL DESIGN: https://www.youtube.com/watch?v=pvIVMEFQokE

Second, you should not filter your fact tables directly. You should filter dimensions which have a 1:many relationship(s) with the fact table.

Thirdly, you should know how filtering works when you use CALCULATE. Filters in the inner CALCULATE OVERWRITE filters in the outer CALCULATE. Filters inside CALCULATE are put together with the AND condition. You can change the OVERWRITE behoviour into AND using KEEPFILTERS.

Having said the above (and trust me, it's not to be taken lightly if you want to know what your formulas calculate), you can try this:

TOTAL_ACTUAL :=
CALCULATE (
    SUM ( fact_SAP_JournalEntries[PRIMARY_AMOUNT] ) * ( -1 ),
    keepfilters( 'dim_FCCs'[FOUR_POINT_DESCRIPTION] ) <> "RECLASS" ),
    keepfilters( fact_SAP_JournalEntries[Remapped] = "No" ), -- this should be in a dimension!
    except( -- centers should be in a dimension!
    	VALUES( fact_SAP_JournalEntries[TTH_CENTER],
    	{ "737000100", "737999994", "737999999"}
    )
)

SubsetValue :=
CALCULATE (
    [TOTAL_ACTUAL],
    keepfilters( dim_FCCs[FCC] ) = "737103001" )
    keepfilters(
    	treatas(
    		{"21601000", "21601003"},
    		dim_Accounts[ACCOUNT]
    	)
    )
)

Best

Darek

[GertW]

Hi Darek,

Thank you once again for your thoughtful help - it works like a charm! I also made the changes you suggested (i. e. filtering the dimensions rather than the facts.

Just in case somebody is following the thread, my final code for the main measure TOTAL_ACTUAL is

TOTAL_ACTUAL:=
CALCULATE (
    SUM ( fact_SAP_JournalEntries[PRIMARY_AMOUNT] ) * ( -1 ),
    KEEPFILTERS ( dim_Accounts[FOUR_POINT_DESCRIPTION] <> "RECLASS" ),
    EXCEPT (
        VALUES ( dim_FCCs[FCC]),
        { "737000100", "737999994", "737999999" }
    ))

and for the Subset_Value

Subset Value:=
CALCULATE (
[TOTAL_ACTUAL],
KEEPFILTERS ( dim_FCCs[FCC] = "737103001" ),
KEEPFILTERS ( TREATAS ( { "21601000", "21601003" }, dim_Accounts[ACCOUNT] ) )
)

I learned a huge lot from your reply, so truly, thank you very much!!

Gert

[GertW]

Hi Darek,

Thank you very much for all your valuable information, and for even taking the time to put some proper code together!

I'm working on it right now and I'll post an update here shortly.

Much appreciated,

Gert