Forum Discussion
Is there an API to get possible filter values (for buiding custom filter UI)?
We're using pbi embed to offer embedded reports in our custom solution. This works fine.
The next step is to offer a way to apply filters to these reports. I know it's possible to apply filters before loading or after loading a report (or visual). This works fine too if the filter is set up manually.
The challenge is to show the available values (options?) to the user to filter on.
For instance: let's say I want to offer a filter on product category (which is a column in a table in the underlying dataset). Is there a way to retrieve the possible product categories to present to the user? I've looked at all the API's but can't find anything that points me in the right direction. Or is it simply not available from the Power BI API's and would you have to build a connection to the actual data source yourself?
Since this functionality is available in Power BI itself I was hoping there's API's to get this data myself, too!
Mark
Hi Lumpie,
AMO-TOM gives you full access to the tabular object model, including the table names, column names & data types.
Alternatively, you can use AMO-TOM to query DMVs (e.g. select * from $SYSTEM.TMSCHEMA_TABLES).
In the Power BI APIs front, you can use the scanner APIs.
However, those are probably not suited for your use case (asynchronous, the metadata may not be up to date etc.).
9 Replies
Hi Lumpie,
You can use this API to run a DAX query like "Evaluate Values(TableName[ColumnName])".
This will result in the distinct list of values from TableName[ColumnName], including a possible blank row due to broken referential integrity.
It is the same logic used by Power BI itself to populate slicers.
Note that if your tabular model is hosted on AAS/SSAS, you cannot use this API.
In this case, you can use the AMO-TOM assemblies to send the DAX query.
Those assemblies can be used to connect to any tabular model through its XMLA endpoint.That looks promising! I'll have a look at this.
Is there a way to get the available tables and/or columns this way as well? Or do you need to 'know' these beforehand?
Thanks for the help!
Mark
Hi Lumpie,
AMO-TOM gives you full access to the tabular object model, including the table names, column names & data types.
Alternatively, you can use AMO-TOM to query DMVs (e.g. select * from $SYSTEM.TMSCHEMA_TABLES).
In the Power BI APIs front, you can use the scanner APIs.
However, those are probably not suited for your use case (asynchronous, the metadata may not be up to date etc.).
