Forum Discussion

Torbennb's avatar
Torbennb
Frequent Visitor
1 year ago
Solved

Call odata feed direct from report

I want to build a report, where I can filter on groups of users. 

With Odata I can do this call:

/services/users/Groups

Returns the list of active groups

/services/users/Groups/{groupId}/members

Returns the list of active group members
 
But I need to have an active group id to call the members of the group. I can´t get the entire list of groups and group members.
 
Are there anyway in Power BI an uption that Power Bi makes this call when I select i group in the filter in the report?
 
Of course it´s possible to run a power automate solution that calls the group members, but I want the interface to be Power bI for the end users.

 

 

Need Help
  • Akash_Varuna's avatar
    Akash_Varuna
    1 year ago

    Hi Torbennb  There can be many approaches i am mentioning a few

    • Preloading Data:
      Use Power Automate to fetch all groups and their members from the OData API, store the data in a SharePoint list, database, or Azure Blob Storage, and load it into Power BI. Users can filter groups and see members seamlessly.

    • Dynamic Data Retrieval:
      Use Power BI Parameters and Power Query to dynamically call the API for group members based on a selected Group ID. Limited scalability for large datasets.

    • Custom Visual:
      Create a custom Power BI visual or use Power BI Embedded with JavaScript to call the API dynamically when a group is selected.
      If this post helped please do give a kudos and accept this as a solution
      Thanks In Advance

     

     

  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi Torbennb,

     

    Thanks Akash_Varuna  for addressing the issue.

     

    There are some limitations to consider based on the points provided by the super user:

    • No Query Folding: Power BI does not support query folding for OData with dynamic parameters, which may require an on-demand refresh.
    • Real-Time Updates: DirectQuery may not work for real-time updates, so Power Automate might be the best approach.

    If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it!

     

    Regards,

    Vinay Pabbu

4 Replies

  • Akash_Varuna's avatar
    Akash_Varuna
    Super User
    1 year ago

    Hi Torbennb  There can be many approaches i am mentioning a few

    • Preloading Data:
      Use Power Automate to fetch all groups and their members from the OData API, store the data in a SharePoint list, database, or Azure Blob Storage, and load it into Power BI. Users can filter groups and see members seamlessly.

    • Dynamic Data Retrieval:
      Use Power BI Parameters and Power Query to dynamically call the API for group members based on a selected Group ID. Limited scalability for large datasets.

    • Custom Visual:
      Create a custom Power BI visual or use Power BI Embedded with JavaScript to call the API dynamically when a group is selected.
      If this post helped please do give a kudos and accept this as a solution
      Thanks In Advance

     

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi Torbennb,

     

    Thanks Akash_Varuna  for addressing the issue.

     

    There are some limitations to consider based on the points provided by the super user:

    • No Query Folding: Power BI does not support query folding for OData with dynamic parameters, which may require an on-demand refresh.
    • Real-Time Updates: DirectQuery may not work for real-time updates, so Power Automate might be the best approach.

    If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it!

     

    Regards,

    Vinay Pabbu

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      Hi Torbennb,

       

      As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
      If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

       

      Regards,
      Vinay Pabbu

  • Torbennb's avatar
    Torbennb
    Frequent Visitor
    1 year ago

    Thanks to all for clarifying this for me. It seems that I need to go another direction to my reports. Another request related to this. Developers have suggested to build a new endpoint with this structure.

    Would that be possible to use in Power Bi:

    [
      {
        "GroupId": "1",
        "MemberIds": [
          "1",
          "2",
          "3",
          ...
        ]
      },
      ...
    ]

    I have the relation between the memberid and the member in another table.