How to use multiple tables in single filter in DAX?

[Narender]

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.AMOUNT]),
FILTER ( ALL(TAX_TRANSACTION),TAX_TRANSACTION[TAX_SUB_TRANS.CHARGE_TYPE_NO]=2)) 

I have to use one more table TAX_SUB_TRANS in the filter including some condition

TAX_TRANSATION(TAX_SUB_TRANS.[YEAR]) = 2015

So I want to use the multiple tables in this filter block. Please have a look towards it and help me to resolve this issue.

Thanks 

Narender

[dasingh9 · Accepted solution]

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

[Narender]

Hello Mr Singh,

I am getting a problem . when i add

 Year in axis and measure as you mention above , It showed the same amount for the year like 2015, 2016, 2017.

Actually Year is from calender table and measure is from diffrent table which are connected in relationships view.  

how i can get the correct amount??

Thanks

Narender

[Stachu]

the Year filter context will be propagated from the visual itself - you don't need to hardcode it in the formula (unless you always want to show single year regardless of the selection)
Is the use of ALL necessary? can you explain if the formulas below suit your need?

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

alternatively 

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

[Narender]

I teried with the both conditions as you mention in your reply.

But i get the wrong result.

It is showing same amount for all year

like 2011 -23$

       2012-23$

       2013-23$

In actual all have different amount as per year.

year is the axis in chart.

Thanks,

Narender

[Stachu]

Year is coming from different table? are they joined properly? can you share example of the tables you're using & the relationship schema?

[Narender]

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

[Narender]

Please see the below relationships .

[Stachu]

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]

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]

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]

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])
)

[Stachu]

you can pass multiple filter criteria in CALCULATE, thay are evaluated with AND operator

CALCULATE (
    SUM (TAX_TRANSACTION[TAX_SUB_TRANS.AMOUNT]),
    FILTER ( ALL(TAX_TRANSACTION),TAX_TRANSACTION[TAX_SUB_TRANS.CHARGE_TYPE_NO]=2),
    new filter expression
)