topic Re: Learning in DAX Commands and Tips

Learning

Anonymous — Mon, 20 Apr 2020 05:07:00 GMT

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

Re: Learning

nandukrishnavs — Mon, 20 Apr 2020 06:35:13 GMT

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 🙂

Re: Learning

Anonymous — Mon, 20 Apr 2020 08:37:51 GMT

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

Re: Learning

nandukrishnavs — Mon, 20 Apr 2020 09:18:23 GMT

Anonymous Thanks