Analyzing Navision data in Power BI

In earlier posts, we have learned Power BI on sample data like IPL data. However when it comes to actual requirements, we would need Power BI reports or dashboards on real data from business applications such as ERP, CRM or any other application.

Here lets see how we can connect to Navision 2016 (NAV) data from Power BI. Navision 2016 might be deployed in different ways. It can be completely on premise in the office premises or the other way which is more common these days – on Azure server.  Here database server and NAV application server both would be on Azure. Let us see focus on NAV on Azure from Power BI and develop reports.

Exposing Data from NAV

First we need to understand how to expose data from NAV so that it can be used in Power BI and analyzed. Someone who is NAV technical, would understand it in depth, but let me tell you the concept. We have to create the query based on the requirement and publish it to make it accessible from Power BI or any third application in that case. Its exactly similar how we prepare NAV for any third party integration. Either we can create a query or a custom page with CAL code behind it for the business logic and publish it. You probably can go for any of these methods based on your comfort and ease. I am not going in details of how to do this as we can approach NAV technical team and get this done.

Kindly refer below Microsoft links to know more about creating and publishing queries

How to create queries in NAV

How to publish a web service in NAV

One more important aspect of NAV connectivity is securing the connection with certificate. We can refer below link for more details on this

How to configure ssl to secure the connection to Microsoft Dynamics NAV web client

If we only want to try this connectivity without any security,  we can configure such binding with only http instead of https and test the connectivity without any certificate installation.

Once we have these pages or queries published, we can check the details by below mentioned steps

Open NAV and search for web in the right top as mentioned in below snippet

Checking the webservice

Once we search for  web, we can see web services in the list below as shown in below snippet. Checking the webservice1

Let us click on the Web services, which will take us to the Web Services section. Here all published web services are listed. As I mentioned earlier, here we are referring to CRONUS database. Microsoft has already provided out of box queries and pages which are published and can be accessed from other applications.

There is one more way to access the web services as shown in below screenshot from the left section.app8

Power BI Content Packs/Power BI Apps

At the time of writing this blog, Microsoft has deprecated Content Packs and focusing on Power BI Apps

For basic understanding of different ways to showcase and analyse NAV data in Power BI, Microsoft provides Power BI content pack for Navision. This content pack is free to use and can connect to Navision on Azure. We just have to establish the connectivity and all reports and dashboards are readily available. In case any of our implementation is vanilla implementation, we can directly use this content pack for Power BI dashboards. We just have to keep it mind that the reports and dashboards which are auto created by content pack can not be modified. Hence I would suggest to refer these reports and recreate on our own so that we have complete control on the same. 

Power BI content pack uses below out of box queries as shown in below snippet,

  • ItemSalesAndProfit
  • ItemSalesByCustomer
  • SalesDashboard
  • SalesOpportunities
  • SalesOrdersBySalesPerson
  • TopCustomerOverview


We can also see powerbifinance page in below snippet, which is also used by Power BI content pack.



Let us go further and actually use one of the web service (Sales Order by Sales person) in the Power BI. As we can see in the below snippet, sixth column oData URL shows the actual oData URL for that query or page.


As we select that row and right click , we can see an option of Copy cell. Lets select that option and copy the URL in clipboard.


If we see at the oData URLs for all the required queries and pages, we will find that entire URL is same except the last section after “\”.

• ItemSalesAndProfit‘CRONUS%20India%20Ltd.’)/ItemSalesAndProfit
• ItemSalesByCustomer‘CRONUS%20India%20Ltd.’)/ItemSalesByCustomer
• Powerbifinance‘CRONUS%20India%20Ltd.’)/Powerbifinance
• SalesDashboard‘CRONUS%20India%20Ltd.’)/SalesDashboard
• SalesOpportunities‘CRONUS%20India%20Ltd.’)/SalesOpportunities
• TopCustomerOverview‘CRONUS%20India%20Ltd.’)/TopCustomerOverview

When we are going to use the content pack, we just need to use the URL which is highlighted in blue color i.e. “‘CRONUS%20India%20Ltd.’)“. Rest would be managed by content pack logic.

Let us see how to do that in Power BI. Let us login to Power BI service and we can see Apps in the left section. To get the Power BI content pack let us click on Apps. If you already have any Apps installed, it would show those in the right section.However for me nothing is installed as of now.


Now we need to get the Power BI apps for Navision which are already published in Microsoft AppSource. Hence let us click on Search in AppSource link as shown below.


By Default it shows all the available apps.


We have search for the apps for Navision as shown in below snippet.As we are talking about NAV 2016, we have below three apps –

  1. Microsoft Dynamics NAV – CRM Report
  2. Microsoft Dynamics NAV – Financials Report
  3. Microsoft Dynamics NAV – Sales Report

Other three apps are for NAV 2018 which are not in scope of this blog.


Let us go ahead and select any of the above app and click on Get it now button to install the app in our Power BI service as shown in below snippet


Once we click on the Get it Now button, installation would start and we would get below screen asking for the NAV web service URL. Here we need to enter the oData URL which was mentioned above and marked with blue color. We need not worry if this sales report and which web service it would actually require.


Once we enter this url and click next, this app would complete the installation,  Power BI reports and dashboards would be automatically created.

We can see all required components automatically created such as Dataset, report and dashboards etc. Here we can visualize and analyze NAV data in Power BI dashboards.

Below are some sample snippets from the reports

Only drawback of this app is we can not edit this App for any changes in Dataset, report or dashboards. Hence I would recommend to use these Apps for demo purpose and showcase the Power BI capabilities with NAV data. Then based on actual requirements we can actually develop the required components.

I have explained the Power BI with NAV in this blog. The similar concept goes with any other ERP or any other business application. We only need to know how to expose the data from that application and make it available in Power BI.

If you like the blog, kindly hit the like button or share it with colleagues.  Also let me know in case you want me to write on any other topic. I will try to write about it.



Power BI: Measures Vs Calculated Column

In earlier blogs, we have understood various jargons in Power BI. To add to these jargons further,  there is concept called Measures in Power BI. Generally this term gets confused with calculated columns.

Let us see what Measures is all about and if it is really an alternative to Calculated columns. Actually its not!! 

We have seen how to add calculated column in earlier blogs such as Win_by_Runs. Here we wanted to know the number of runs by which the match was won, and it was for every match. Hence the calculation was for row wise and result is shown row wise. 

In short, Calculated columns use the context of the row and provide calculation row wise. Hence whenever we need any such calculation at row level, we have to use the calculated column. 

On the other side, if we need any calculation as aggregation of the entire data or based on any filter, we have to use Measures. In our IPL data, if we want to see total runs for all matches, we need this as aggregation of values of one column and all rows. This can be achieved by Measures. 

Let us go back to our IPL example again to see how it works. If we want to know total runs scored. Below are the steps to create the Measure. 

Navigate to Modelling tab, Select the table in which you want to add the measure and click on New Measure as shown in below snippet.


By default the Measure 1 will be the name for this Measure, depending on how many measures we have already added in this. Here I already have few measures created, hence its showing Measure 3 as shown in below snippet.


Let us change the name and add the formula as shown in below snippet. 


Here we have to understand that, unlike the columns, Measure will not be seen as a column in the table, and will be available only as formula to be calculated at runtime.

Hence we won’t be able to see the result of this formula now.


If we want to see the output of this measure, we need to add this to Report and can see the value. Either we can directly drag this measure to report and see the bar chart by default or add a card visual and then select the measure as field. This will calculate the value of the measure and display on the visual.


We can also add filters to the measures. If we want to see the total runs scored in Mumbai location, we can write use the formula as Calculate and add filter using match formula as shown in below snippet.Measure7

If we see the output of this filtered measure, we can see total runs scored in Mumbai as shown in below snippet.


Summing up the Measures vs Calculated Columns comparison considering three parameters

  • Memory utilization:
    • Calculated column is stored in the in memory of the database and it will consume the memory even if nobody is querying this column. However while user interacts with reports, there is no impact on user’s virtual memory utilization
    • Measures is always calculated at run time, it actually impacts the virtual memory, however it saves in memory of the database.  Hence one should be careful while adding measures in the Power BI
  • Evaluation Context: 
    • Calculated column to be used when calculation is in row context 
    • Measures to be used when aggregation is expected from multiple rows
  • Visibility
    • Calculated columns’ output is visible in the table as soon as they are created 
    • Measures’ output is not visible until we add it to the report in terms of visual

Thank you for reading my blog, if you have any questions please mention in the comments and hit the like / share button if you like my article.

Keep Learning !! Happy analyzing and be Powerful 🙂

Continue reading “Power BI: Measures Vs Calculated Column”