Change Direct Query of Visual

[Fernandofrancis]

Hello,

I'm using Direct Query to access a large table in an Oracle Database.  Is it possible to change the query that power bi send to the database, to fill a Card for exemple? I got the query using Performance Analyzer and I think is possible improve the performance just changing the its syntax. Another exemple is the calendar dimesion, when I set two dates the power bi uses IN clause: "DTHORA IN (TO_DATE('2020-08-05 00:00:00', 'YYYY-MM-DD HH24:Mi:SS'), TO_DATE('2020-08-06 00:00:00', 'YYYY-MM-DD HH24:Mi:SS') ...)"

When the best way to do that is use clause BETWEEN.

[amitchandak]

@Fernandofrancis , not possible to change query. But can try to change the measure to see if you get the desired query

[nandukrishnavs · Accepted solution]

@Fernandofrancis You cannot directly modify the query generated by Power BI. But you can change the DAX measure and you can see a slight variation in the SQL query. In addition, you could write a native query to calculate the value of the visual card and use that table on the card. But you may lose interaction between other visuals.

When you use the archived date in the slicer, it will generate the IN condition. An alternative option is to use Integer instead of the date field.