cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Lumpie
Regular Visitor

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

1 ACCEPTED SOLUTION
SpartaBI
Community Champion
Community Champion

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.).



2022-05-19 17_30_22-Re_ Need help on DAX function with measure vs colu... - Microsoft Power BI Commu.png

Showcase Report – Contoso By SpartaBI


SpartaBI_3-1652115470761.png   SpartaBI_1-1652115142093.png   SpartaBI_2-1652115154505.png

Full-Logo11.png

View solution in original post

9 REPLIES 9
SpartaBI
Community Champion
Community Champion

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.).



2022-05-19 17_30_22-Re_ Need help on DAX function with measure vs colu... - Microsoft Power BI Commu.png

Showcase Report – Contoso By SpartaBI


SpartaBI_3-1652115470761.png   SpartaBI_1-1652115142093.png   SpartaBI_2-1652115154505.png

Full-Logo11.png

SpartaBI
Community Champion
Community Champion

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.

 


2022-05-19 17_30_22-Re_ Need help on DAX function with measure vs colu... - Microsoft Power BI Commu.png

Showcase Report – Contoso By SpartaBI


SpartaBI_3-1652115470761.png   SpartaBI_1-1652115142093.png   SpartaBI_2-1652115154505.png

Full-Logo11.png

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

xhan
Regular Visitor

@Lumpie  were you able to figure out how to get the avaiable tables and columns? 

@SpartaBI can we get in a visual within a report, what are the tables and columns used in some rest APIs? 

 

Thanks a lot!

SpartaBI
Community Champion
Community Champion

Hi @xhan,

You can get the available tables & columns using the AMO-TOM assemblies
Look at my reply from ‎06-23-2022 06:38 PM.

Using the Power BI embedded SDK, you can get the tables & columns defined in a visual. See here.



2022-05-19 17_30_22-Re_ Need help on DAX function with measure vs colu... - Microsoft Power BI Commu.png

Showcase Report – Contoso By SpartaBI


SpartaBI_3-1652115470761.png   SpartaBI_1-1652115142093.png   SpartaBI_2-1652115154505.png

Full-Logo11.png

xhan
Regular Visitor

Thank you @SpartaBI  for the quick reply! I'm pretty new to power BI, and have some general questions.

1. Is AMO-TMO based on sql-server? we are not really using sql-server.

2.The embedded SKD looks like based on JS, I'm curious if there are something similar in the rest APIs?

3. I guess my previous question was not very clear. I'd like to pull all the metadata of the dashboards and reports. In a report, I'd like to know what are the pages, in each page what are the visuals, and what data source table and columns are used in the visuals etc. I was following this https://docs.microsoft.com/en-us/power-bi/admin/service-admin-metadata-scanning-setup to call the admin scanner APIs, but I don't see any table/column/measure data at all. 

 

Thanks a lot!

 

Thanks a lot!

SpartaBI
Community Champion
Community Champion

Hi @xhan,

1. Power BI uses "Analysis Services" as its analytical engine. AMO-TOM enables you to connect to an Analysis Services instance using its XMLA endpoint. If you are using Power BI Premium, you can allow access to the XMLA endpoint and use AMO-TOM. Analysis Services is also included as part of SQL Server (SSAS = SQL Server Analysis Services), but that should not concern you.

2. This functionality is not exposed as part of the documented Power BI REST APIs.

3. AFAIK, the scanner APIs do not return metadata at the page or visual granularity. If you set both tenant settings in the doc & performed a refresh, You should be able to get table/column/measure. See this doc regarding limitations.
This is an area in which Power BI lacks functionality. There are APIs to get some details for the pages & dashboards. See
get-pages-in-group
get-dashboards-in-group
get-tiles-in-group
You also have the option to get the structure directly from PBIX files by using 3-party (unsupported) tools like PBI Tools 
Those parse the pages & visuals metadata. See, for example here (19:00).
If you need to export reports to PBIX files you can use
export-report-in-group 



2022-05-19 17_30_22-Re_ Need help on DAX function with measure vs colu... - Microsoft Power BI Commu.png

Showcase Report – Contoso By SpartaBI


SpartaBI_3-1652115470761.png   SpartaBI_1-1652115142093.png   SpartaBI_2-1652115154505.png

Full-Logo11.png

xhan
Regular Visitor

thank you @SpartaBI 

SpartaBI
Community Champion
Community Champion

@xhan my pleasure.

Come follow us 🙂

Helpful resources

Announcements
August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power BI Dev Camp Session 24 without aka link and time 768x460.jpg

Ted's Dev Camp - July 28, 2022

Watch Session 24 of Ted's Dev Camp along with past sessions!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.