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

dasingh9's avatar
dasingh9
Frequent Visitor
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

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

    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's avatar
      Stachu
      Icon for Community Champion rankCommunity Champion
      8 years ago

      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's avatar
        Narender
        Icon for Resolver I rankResolver I
        8 years ago

        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