How Power BI Field Parameters works under the hoods
There has been a lot of talk (on Twitter) with regards to the new Field Parameters in Power BI.
Marco & Alberto have written a great blog post going into all the details as to how field parameters work, what the query looks and a lot more. I would highly suggest you read it here: Fields parameters in Power BI – SQLBI
In this blog post I want to give a visual representation as to how field parameters works and what the current limitations are.
It is important to be aware of the limitations so that you do not get caught out later or you are trying to figure out why it is not working.
I do hope my descriptions and pictures below help you understand how it works and when it does not work!
How are Field Parameters executed?
The field parameter is created in the reporting/visual layer of a Power BI report.
As shown below the field parameter starts off in the reporting/visual layer.
It is then executed as a DAX query to get the results from the field parameter.
Next, the second DAX query is executed using the results from the first DAX query.
Finally, the results from the second DAX query is returned to the reporting/visual layer.
As you can see above the field parameter starts in the reporting/visual layer and is then executed in the local dataset/model.
Why field parameters will not work when using Excel/XMLA/External Tools
One of the current limitations with field parameters is if you connect with Excel, XMLA or External tools is that the field parameters will not work.
As shown below using the example of connecting via Excel, Excel connects directly to the dataset/model.
When this happens, it does not start from the reporting/visual layer. It starts from within Excel.
Because it never starts or gets to the reporting/visual layer it cannot use the field parameter.
How field parameters can work with composite models
When you create a composite model (local imported dataset/model) and connected to a remote dataset/model the field parameters can work.
They will only work with field parameters in the Import Model.
The reason for this is because the import model is the same as just a single dataset/model, where the field parameter exists in the reporting/visual layer.
NOTE: If there is a field parameter in the remote model it will not work because as described in the previous example the DAX query is not interacting with the reporting/visual layer but only on the dataset/model layer.
Thanks for reading I hope that you found this useful and interesting.
As always any comments or suggestions are most welcome!
[…] Gilbert Quevauvilliers figures out how field parameters work: […]
Well written professionals article ,With field parameters you can give the consumer of a report a lot of flexibility about the content of the report
Thanks
Good article, especially explaining impact ion external tool and using composite models.
Another aspect as you know is that Field Parameters are not yet available in Power BI Report Server. If it is useful for others, I wrote how to mimic Field Parameters in PBIRS at the PowerBI Community Blog.
: https://community.powerbi.com/t5/Community-Blog/Field-Parameter-and-Bookmarking-Field-Parameter-Values-In-Power/ba-p/2588120
Thanks for sharing that is really interesting what you did!
Hopefully it will be updated in the next release of Power BI Report server.