Sometimes, just when you think you are getting the hang of the DAX language, something happens that completely baffles you. Such events can cause you to doubt your own learning and progress. But let me assure you – this happens to EVERYONE at sometime or another. In this article I am going to show you one such baffling situation, and then explain what is really happening.
As a quick follow-on from last week’s post on how to detect whether query folding is taking place when importing from OData data sources, if you’re importing data from Analysis Services you have a similar problem: how do you know whether query folding is taking place? Ensuring that query folding takes place for as many of the steps in your query – especially those that filter or otherwise reduce the amount of data returned – is very important for data refresh performance.
◾The requirement was such that , the user will select a line in the subform.
◾The subform has fields like the Customer ID, Customer Name, and a field Recieved Crates.
◾On click of the action button in the Main form, a page opens which contains the details of the selected line in the subform like Customer ID and Customer Name also a field Crate recieved(Integer Datatype).
A lot of people have problems with the performance of OData data sources when loading data into Power BI and Excel. One possible cause of these problems is query folding not taking place – if this is the case then the Power Query engine will be requesting more data that is needed and applying any filters itself locally, rather than requesting filtered data from the data source. How do you know whether query folding is taking place or not though?
Here is the scenario. The user wants to select a product from a list. When that product is selected, they want to see a list of all invoices that contain that product AND ALSO see ALL OTHER PRODUCTS on those same invoices. The reason this is such an interesting problem is that normally when you select products from a list in Power Pivot, the selected products filter the data model hence you can only see sales for those selected products