Forum Discussion

Narender's avatar
Narender
Icon for Resolver I rankResolver I
8 years ago
Solved

How to use multiple tables in single filter in DAX?

Hello   I am using the DAX expression during preparation of chart. I have some problem in it. I want to use multiple tables in the filter like:   CALCULATE ( SUM (TAX_TRANSACTION[TAX_SUB_TRANS....
Need Help
  • dasingh9's avatar
    dasingh9
    8 years ago

    Hi Narender,

     

    You may use the AND operator to put another condition to the expression. You may even use multiple conditions in the expression as per your requirement. Here is an example how you could do that.

     

    CALCULATE (
    SUM (TAX_TRANSACTION[TAX_SUB_TRANS.AMOUNT]),
    FILTER ( ALL(TAX_TRANSACTION),TAX_TRANSACTION[TAX_SUB_TRANS.CHARGE_TYPE_NO]=2) AND TAX_TRANSATION(TAX_SUB_TRANS.[YEAR]) = 2015)

     

    Cheers,

    Davinder

Stachu's avatar
Stachu
Icon for Community Champion rankCommunity Champion
8 years ago

there are multiple date fields in TAX_TRANSACTION, are they connected properly?
based on what I see this measure should work fine, as long as you use 'Dates 5'[Year]

CALCULATE (
    SUM (TAX_TRANSACTION[TAX_SUB_TRANS.AMOUNT]),
    TAX_TRANSACTION[TAX_SUB_TRANS.CHARGE_TYPE_NO]=2
)

 in your ealrlie rpost you mentioned also TAX_SUB_TRANS.[YEAR] - this one seems to not be connected with TAX_TRANSACTION, therefore filter will not propagate

dasingh9's avatar
dasingh9
Frequent Visitor
8 years ago

Yes, looks like that [TAX_TRANSACTION] and [TAX_SUB_TRANS] tables should be connected based on TAX_TRANS_NO column.

 

OR

 

EnterDate column of [TAX_SUB_TRANS] table should be connected with Date column of [Dates 5] table.

  • Narender's avatar
    Narender
    Icon for Resolver I rankResolver I
    8 years ago

    I am giving you an example like

     

    I have 3 tables.

     

    Tax_transaction,Tax_Sub_Transacation,Date5

     

    Tax_transaction and Tax_Sub_Transacation, are conneceted via left join in power query.

     

    After that combination of "Tax_transaction and Tax_Sub_Transacation table"  is conneted via Date of Date5 table in relationships view.

     

     

    In my chart ,I have to show the AMOUNT of tax_sub_transaction table (which is connected via left join to Tax_transaction)

    and charge_type =2  of tax_sub_transaction table and Year will show in axis of chart which a field of Date5 Table.

     

     

    Thanks,

     

    Narender

  • Stachu's avatar
    Stachu
    Icon for Community Champion rankCommunity Champion
    8 years ago

    from the picture you posted it seems that the joins are one direction only, so filter from 'Tax-Sub_Transaction' will propagate to 'Tax_Transaction' but not to 'Date 5' (and the other way round). Changing the relationships to bidirectional may help, but I'm not sure that's the best approach

    otherwise you can create a measure based on 'Tax_sub_transaction' and 'Date 5' even if they're not joined in a way that propagates filter context ( you may need to change the date column in TAX_SUB_TRANS)

    CALCULATE (
    SUM (TAX_SUB_TRANS[AMOUNT]),
    TAX_SUB_TRANS[CHARGE_TYPE_NO]=2,
    INTERSECT(ALL(TAX_SUB_TRANS[ENTER_DATE]),'Date 5'[Date])
)