Using FILTER on two tables

[gvg]

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?

[v-sihou-msft · Accepted solution]

@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,

[gvg]

Thanks @v-sihou-msft !

[Ashish_Mathur]

Hi,

Share a sample dataset and describe your business question.

[gvg]

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.