Forum Discussion
Helper IIIAverage not in date period
What is the equivalent DAX of the following SQL? i.e. vends not in date period...
SELECT AVG(CountRows)
FROM pbi.FactVend AS FV
JOIN pbi.DimAsset AS DA ON DA.KEY_Asset = FV.KEY_Asset
WHERE CAST(FV.KEY_VendDate AS Date) NOT BETWEEN DA.ExcludedFromDate AND DA.ExcludedToDate
Something along the lines of (can't get DAX right)
Average Cup Vends =
CALCULATE(AVERAGE(Vend[CountRows]), CONVERT(Vend[KEY_VendDate], DATETIME)
NOT IN FILTER(Asset, DATESBETWEEN(CONVERT(Vend[KEY_VendDate], DATETIME), Asset[Excluded From Date], Asset[Excluded To Date])))
The answer to this was simply to add the keyword
RELATED ( Asset[Excluded From Date] ),RELATED ( Asset[Excluded To Date] ) to my fields from the non filtered table.
5 Replies
- amitchandak
Super User
jhowe1 , Try a measure like
Average Cup Vends =
CALCULATE(AVERAGE(Vend[CountRows]), FILTER(Asset, not(DATESBETWEEN(CONVERT(Vend[KEY_VendDate], DATETIME), Asset[Excluded From Date], Asset[Excluded To Date]))))
jhowe1Thanks but this won't work, the rows i want to filter are in 'Vend', but the dates i want to filter by are in Asset (two different tables). It also doesn't seem to like converting my KEY_VendDate into a datetime this is in format int YYYYMMDD.
There is a relationship between these two tables
- jhowe1
Can someone help me finish this please? I simply need to filter the vends where the venddate is not in the asset table period excluded dates Asset[Excluded From Date], Asset[Excluded To Date]...
- jhowe1
The answer to this was simply to add the keyword
RELATED ( Asset[Excluded From Date] ),RELATED ( Asset[Excluded To Date] ) to my fields from the non filtered table.
