Forum Discussion

ATREZISE's avatar
ATREZISE
Helper I
6 years ago
Solved

Filter rows based on linked table

HI All   I'm a relative novice to DAX and finding this a bit tricky.   I have two tables - one with transactions and another with activity codes. The tables are linked on the activity code.   I...
Need Help
  • v-xicai's avatar
    v-xicai
    6 years ago

    Hi ATREZISE ,

     

    You may create column or measure like DAX below. The ALLSELECTED function will take effect of the slicers or filters for measure, while it will take effect when the data be changed and refreshed for the calculated column.

     

    Column1 = CALCULATE (SUM ( Txns[Sales Amount] ),FILTER (ALLSELECTED(Txns), Txns[activity code]= (activity[activity code] ))
 
Measure1 = CALCULATE (SUM ( Txns[Sales Amount] ),FILTER (ALLSELECTED(Txns), Txns[activity code] IN FILTERS (activity[activity code] )))

     

    Best Regards,

    Amy

     

    Community Support Team _ Amy

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

TomMartens's avatar
TomMartens
Super User
6 years ago

Hey,

 

it's not possible to create a CALCULATED COLUMN that reflects the selection from a slicer. The formula of a CALCULATED COLUMN will ony be evaluated during data refresh and if the formula will be changed.

This means you have to create a measure.

 

And you have to be aware that the FILTER formula just  has to parameters. Multiple conditions have to be combined like so:
FILTER( <expression> ; condition1 && condition2 || condition3

&& means logical AND

|| means logical OR

It's possible to use parenthesis defining the filter

 

Hopefully this helps

 

Regards,

Tom

ATREZISE's avatar
ATREZISE
Helper I
6 years ago

Thanks for the quick reply

 

It may be easier to explain it another way?

 

I am trying to sum a column from the transactions table and store the result somewhere (probably better as a measure) where the activity code linked to the transaction existing in the activity code table. The transactions are already filtered by year / month.