Learning

[Anonymous]

Hi

I am experimenting with the following but finding that the filter criteria is not working as i expected but not sure why from what i have read.

2019-20 ACTUAL Income EXCL 100 =  CALCULATE(

[2019-20 Totals],

NOT BIGL_BSCC_DATA_181920[ACCT_CATEGORY] IN {"100"},

'BIGL_GL_DATA_181920V2'[INCOME_EXP] IN { "ACTUAL Income" }

)

The goal is to sum the values but exclude 100 have i done something wrong?

Thanks in advance

[nandukrishnavs]

@Anonymous 

Try this measure

2019-20 ACTUAL Income EXCL 100 =
CALCULATE (
    [2019-20 Totals],
    FILTER (
        'BIGL_BSCC_DATA_181920',
        NOT ( BIGL_BSCC_DATA_181920[ACCT_CATEGORY] IN { "100" } )
    ),
    FILTER (
        'BIGL_GL_DATA_181920V2',
        'BIGL_GL_DATA_181920V2'[INCOME_EXP] IN { "ACTUAL Income" }
    )
)

 If this is not working, please share sample dataset and logic of [2019-20 Totals]

Did I answer your question? Mark my post as a solution!
Appreciate with a kudos 🙂

[Anonymous · Accepted solution]

Hi @nandukrishnavs 

A better formulation of your measure is this:

2019-20 ACTUAL Income EXCL 100 =
	CALCULATE (
	    [2019-20 Totals],
	    KEEPFILTERS(
	    	BIGL_BSCC_DATA_181920[ACCT_CATEGORY] <> "100"
	    ),
	    KEEPFILTERS(
	    	'BIGL_GL_DATA_181920V2'[INCOME_EXP]  = "ACTUAL Income"
	    )
	)

It's better in 2 ways. First, it's a bit less to type. Second, it's more performant for 2 reasons:

1) you should not put a full table as a filter in CALCULATE if there's no real need (there seldom is),

2) KEEPFILTERS is faster.

And the golden rule of DAX says: Never filter a table when you can filter a column.

All these things can be discovered through www.sqlbi.com, the site by The Italians.

Best

D

[nandukrishnavs]

@Anonymous  Thanks