Forum Discussion

Lumpie's avatar
Lumpie
Frequent Visitor
4 years ago
Solved

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 b...
Need Help
  • SpartaBI's avatar
    SpartaBI
    4 years ago

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



    Showcase Report – Contoso By SpartaBI


          

SpartaBI's avatar
SpartaBI
Community Champion
4 years ago

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.

 


Showcase Report – Contoso By SpartaBI


      

Lumpie's avatar
Lumpie
Frequent Visitor
4 years ago

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's avatar
    xhan
    Helper I
    4 years ago

    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!

      • xhan's avatar
        xhan
        Helper I
        4 years ago

        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!