Forum Discussion

sachin_kalra's avatar
sachin_kalra
New Member
9 years ago
Solved

Filter is not working in Direct query mode

HI Forum

i am using direct query with sql and trying to calculate a value using

TotalBudget = CALCULATE([SumBudget],FILTER(Fact_CE_AM_Revenue,Fact_CE_AM_Revenue[year]=MAX(Fact_CE_AM_Revenue[year])))

but i am facing below error

Function 'FILTER' is not supported in this context in DirectQuery mode.

Note : i have a year column by which budget is differentiated

it will be really helpful if you could help me out

Reports

8 Replies

    • ptalanki's avatar
      ptalanki
      New Member
      8 years ago

      Enabling below option did not work. Below is our DAX query - 

      MTD_Avg_Balance_Debt = TOTALMTD([Sumprin Debt],v_get_TransDate[TransDate])/(DATEDIFF(STARTOFMONTH(v_get_TransDate[TransDate]),max(v_get_TransDate[TransDate]),DAY)+1)

       

      Works on desktop version, howver doesnt work once published to sever 

       

      Power BI

      MdxScript(Model) (23, 54) Function 'DATESMTD' is not supported in DirectQuery mode.
      Please try again later or contact support. If you contact support, please provide these details.
      Request ID0d017378-356c-923e-4938-8a86f54d5201
      TimeFri Mar 16 2018 14:44:39 GMT+0530 (India Standard Time)
      Version14.0.600.434
    • ptalanki's avatar
      ptalanki
      New Member
      8 years ago

      Enabling below option did not work. Below is our DAX query - 

      MTD_Avg_Balance_Debt = TOTALMTD([Sumprin Debt],v_get_TransDate[TransDate])/(DATEDIFF(STARTOFMONTH(v_get_TransDate[TransDate]),max(v_get_TransDate[TransDate]),DAY)+1)

       

      Works on desktop version, howver doesnt work once published to sever 

       

      Power BI

      MdxScript(Model) (23, 54) Function 'DATESMTD' is not supported in DirectQuery mode.
      Please try again later or contact support. If you contact support, please provide these details.
      Request ID0d017378-356c-923e-4938-8a86f54d5201
      TimeFri Mar 16 2018 14:44:39 GMT+0530 (India Standard Time)
      Version14.0.600.434
      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        I have a similar issue, i.e. you can get away with unrestricted measures in PBI Desktop only for it to fail on the Reporting Server. Did you find a viable workaround? Can "unrestricted measures" bet set for the Reporting Server as well, and if so how? Any information would be greately appreciated.

         

        RE