PowerBI and Dynamics AX Integration - filter by data record selection in Dynamics AX

[KARE]

Hi,

all examples concerning the integration of PowerBI  in Dynamics AX show as starting point only totals! So if I would start in a workspace for customer- the PowerBI report show e.g. the turnover, number of order as total for all customers... and then you can drill-down in the PowerBI report to a specific customer.

So may question is, would it also be  possible to select a data record in Dynamics AX and the PowerBI report will only show the values for this selected data record. e.g. I select a customer in Dynamics AX and only the turnover, number of orders, open invoices for this customer is shown in the PowerBI report?

Thanks

Rene

[v-yuezhe-msft]

Hi @KARE,

Based on my research, Power BI Service retrieves data of Dynamics AX 7 based on the Dynamics AX 7 environment URL, there is no place that we can put parameter to get only filtered data for specific customer at the beginning of importing Microsoft Dynamics AX content pack.

As a workaround for this issue, you can consider to connect to Dynamics AX via OData Feed entry in Power BI Desktop, filter rows and model your data in Desktop, then create reports and publish the Desktop file to Power BI service.


Alternatively, you can connect to Dynamics AX via OData Feed entry in Excel, model your data, then create reports and upload the worksheet to Power BI service, for more details, please review this blog.


Thanks,
Lydia Zhang

[KARE]

Hello Lydia,

thanks for you answer. So you don't see the possibility to interact with PowerBI embedded in a Dynamics AX workspace directly by selecting a data-record?

Thanks

Rene

[v-yuezhe-msft]

Hi @KARE,

Yes. Within the content pack, we have less control. Please use Power BI Desktop to connect to Dynamics AX  and filter data based on  your needs.

Thanks,
Lydia Zhang

[retailguy]

Hi Lydia, 

I am currently using the ODATA feed for Dynamics AX online.  We have been told that the tables cannot be filtered using parameters with the ODATA url and so whenever a refresh occurs we must extract ALL rows and ALL columns before filtering on the PowerBi side.  This takes ages and on the service results in timeouts.

Is there a way to filter with parameters as part of the ODATA URL?  I've looked at documentation - https://docs.microsoft.com/en-us/dynamics365/unified-operations/fin-and-ops/  -  this says filtering is possible, but this one says it isn't supported https://blogs.msdn.microsoft.com/axsa/2015/02/20/power-bi-and-dynamics-ax-part-2-extract-and-transfo...

Would yo able able to clarify for me?

thanks