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]))...
Reports
  • v-haibl-msft's avatar
    v-haibl-msft
    9 years ago

    sachin_kalra

     

    Please enable following option and have a try again.

     

     

    Best Regards,

    Herbert

gourangib's avatar
gourangib
Frequent Visitor
8 years ago

Hi,

Did you find any solution for this??

Please update, even I am facing same issue.

 

Regards,

Gourangi

 

MikeDonnellan's avatar
MikeDonnellan
Advocate I
8 years ago

Hi all, this issue is not solved as of May 25, 2018.  We are using Power BI Report Server and the matching version of Power BI Desktop dated March 2018.

 

When we deploy a DirectQuery mode report containing measures like this, with "Allow unrestricted measures in DirectQuery mode" duly enabled

 

LatestCumulativeVarToTarget = 
CALCULATE (
    VALUES ( 'NonAffiliatedOutletSales'[CumulativeVarToTarget] ),
    FILTER (
        'NonAffiliatedOutletSales',
        'NonAffiliatedOutletSales'[CalculateDate]
            = MAX ( 'NonAffiliatedOutletSales'[CalculateDate] )
    )
)

 

and "Save As" to Power BI Report Server, when the report is rendered visuals that use such measures display with the circled X and text "Can't display the visual.  See details."  The details message is:

 

Couldn't load the data for this visual
 
MdxScript(Model) (44, 5) Function 'FILTER' is not supported in this context in DirectQuery mode.
Please try again later or contact support. If you contact support, please provide these details.
Request ID853cc794-8cff-8af9-364f-1d1d6b1a7ec7
TimeFri May 25 2018 08:37:41 GMT-0400 (Eastern Daylight Time)
Version15.0.2.378

 

Not solved.

 

Is this the expected behavior?

  • bboy81905's avatar
    bboy81905
    Frequent Visitor
    8 years ago

    I also have the same problem when I used directquery mode. On the other hand, in import mode, this measure is working.

    LatestTurbidity = CALCULATE(MAXA(MsgJamhamTest1[Value]), FILTER(MsgJamhamTest1,[Type]="Turbidity"&&[UpdateTime]=MAX([UpdateTime])))