Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Learning

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
  • Anonymous's avatar
    Anonymous
    6 years ago

    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

3 Replies

  • nandukrishnavs's avatar
    nandukrishnavs
    Community Champion
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      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