Forum Discussion

Goofr's avatar
Goofr
Advocate IV
9 years ago

Direct Query Where clause on report filter

Hi,

 

I have a report using Direct Query to Azure SQL.

I use a model with 2 tables (Project - Sales) linked on ID (single direction).

When I filter my report on Project.ID the query is fast, when I filter my report on Project.Code, the query is slow.

I know it's a SQL problem, but can't I let the user filter on Code (Displaymember) and behind the scene set the ID (Valuemember) column for the WHERE clause. It's the same record.

 

Any help would be appreciated.

Thanks in advance

 

Frank

Need Help

1 Reply

  • Eric_Zhang's avatar
    Eric_Zhang
    Microsoft Employee
    9 years ago
    Goofr wrote:

    Hi,

     

    I have a report using Direct Query to Azure SQL.

    I use a model with 2 tables (Project - Sales) linked on ID (single direction).

    When I filter my report on Project.ID the query is fast, when I filter my report on Project.Code, the query is slow.

    I know it's a SQL problem, but can't I let the user filter on Code (Displaymember) and behind the scene set the ID (Valuemember) column for the WHERE clause. It's the same record.

     

    Goofr

    Based on my understanding, it is not a behavior that can be controlled by Power BI. Besides, in your case, behind the scene the ID and code are pointing to the same record, however in other scenarios the codes may be duplicated and the ID is the only identifier. I'm not aware of any software that is so intelligent that it would search with A(faster) while you filter on B, as the scenarios may very various.

     

    Just as you said, it is a SQL problem, so you may have to solve it at SQL end, maybe by creating an INDEX on code as well.