Forum Discussion

gvg's avatar
gvg
Post Prodigy
8 years ago
Solved

Using FILTER on two tables

Hi experts,

 

I have a Dates table that is related to Sales table. I also have a slicer for Dates. I need to filter on columns from different tables in my CALCULATE. Something like this (just the FILTER part):

 

FILTER ( ALL ( Date[Date], Sales[Amount] ),
               Sales[Amount]>100
)

However DAX does not allow columns from different tables in ALL. How do I go around this limitation?

Show and Tell
  • v-sihou-msft's avatar
    v-sihou-msft
    8 years ago

    gvg

     

    If you want to filter all dates which the sales is more than 100, you should build a measure for sales amount. Then use this measure as conditon in your FILTER() function.

     

    SalesAmount = SUM(Sales[Amount])

     

     

    FILTER ( ALL ( Date[Date] ),
               [SalesAmount]>100
)

    Regards,

     

4 Replies

    • gvg's avatar
      gvg
      Post Prodigy
      8 years ago

      Well, my data set is quite complicated. Due to lack of space won't be able to reproduce it completely here. I am just looking for valid syntax to overide existing slicer filter on date and then filter according to some other condition on another field in another table.

  • v-sihou-msft's avatar
    v-sihou-msft
    Microsoft Employee
    8 years ago

    gvg

     

    If you want to filter all dates which the sales is more than 100, you should build a measure for sales amount. Then use this measure as conditon in your FILTER() function.

     

    SalesAmount = SUM(Sales[Amount])

     

     

    FILTER ( ALL ( Date[Date] ),
               [SalesAmount]>100
)

    Regards,