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 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.
 
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.

     

5 Replies

  • v-xicai's avatar
    v-xicai
    Community Support
    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.