Forum Discussion

jhowe1's avatar
jhowe1
Helper III
5 years ago
Solved

Average 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 W...
Need Help
  • jhowe1's avatar
    jhowe1
    5 years ago

    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.
amitchandak's avatar
amitchandak
Super User
5 years ago

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]))))

  • jhowe1's avatar
    jhowe1
    Helper III
    5 years ago

    Thanks 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's avatar
      jhowe1
      Helper III
      5 years ago

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