Filter rows based on linked table

[ATREZISE]

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 am trying to create a new column in the activity table to show the some of the transactions that match the specific code.

Column =

CALCULATE (

    SUM ( Txns[Sales Amount] ),

    FILTER (

        Txns,

        FIND ( Codes[Activity], Txns[Activity], 1, 0 ) > 0 ),

        Txns[Year]=SELECTEDVALUE(Txns[Year]),Txns[Month]=SELECTEDVALUE(Txns[Month])

)

I get an error message as PBI doesn't like the SELECTEDVALUE command, however I have a slicer on the form to filter the transactions by year / month so need somehow to restrict the rows.

Any help would be much appreciated.

[v-xicai · Accepted solution]

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.

[ATREZISE]

Thanks

That works perfectly.

[VasTg]

@ATREZISE 

Please provide sample input data and expected output.

[TomMartens]

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]

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.