Using The Power BI Designer by Murray Fife

DYNAMICS AX TIPS & TRICKS

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

Introduction Maybe you thought that the only thing that is better than Power BI within Excel is Power BI running within Office 365, but you are so wrong was wrong. The Power BI Designer that is available within the Office 365 hosted Power BI portal is much better and everyone needs to use it. The Power BI Designer is a standalone dashboard designing tool that takes all of the great features within Excel like Power Query, Power View, and Power Pivot and puts them in one single place to make all of your reporting a synch. And after you have finished building your dashboards, you can then publish them to the new hosted Power BI portal and create your own custom dashboards, and even perform Q&A on the data just by typing in the questions that you want answered.

In this overview we will look at this new tool and how you can use it to quickly analyze all of your Dynamics AX data and also blend in multiple data feeds into one dashboard to view all of the data that you need all in one place. This stuff is too cool!

-2-

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

Accessing The Power BI Dashboard Preview The first step is to access the new Power BI portal within Office 365, and if you already have your existing Power BI portal set up, then this is just a click away.

-3-

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

When you log into your Power BI site, just look up in the top right hand corner, and you should see that there is a note about the Power BI Dashboards feature being available to preview. All you need to do is click on the Try It Now button.

-4-

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

That will take you over to the new Power BI portal and you will be asked to log in using your corporate credentials.

-5-

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

After you have logged in you will be taken to the new and spiffy Power BI Dashboards view. All that you need to do is start building your dashboards.

-6-

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

Adding Excel Datasets To The Power BI Designer To create dashboards through the new Power BI Dashboard tool, you need two things. Data as the ingredients for your dashboards, and Charts to cook the data into something tasty. Lets start by loading in some data into the portal.

-7-

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

To add data, just click on the + sign to the right of the Datasets group heading.

-8-

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

That will open up the Get Data dialog box where you can choose what data you want to make available for reporting.

-9-

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

To pull in some legacy data that you have within Excel spreadsheets, just click on the Excel Workbook data type, select where you want to grab the data from (probably Computer) and then click on the Browse button to search for your excel spreadsheet.

- 10 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

When the File Explorer window is displayed, navigate to your Excel data and click the Open button.

- 11 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

When you return back to the Get Data form, just click on the Connect button to import in all of the data into Power BI.

- 12 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

After a couple of seconds you will see that the new data has been added to your current dashboard panel and you are ready to start doing something with it.

- 13 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

Creating A Chart From The Excel Data Source Once you have the data available within the Power BI portal you can start mixing it up and creating charts and graphs.

- 14 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

To do this, just click on the Data Source that you want to generate the reports from.

- 15 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

This will open up a blank dashboard canvas where all of the fields that were included within the excel file are shown on the right hand side of the form, all of the pages for your dashboard are shown on the right, and the blank reporting canvases is in the middle. Itâ&#x20AC;&#x2122;s time to start looking at your data.

- 16 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

To do this, just click on the Dimension on the right that you want to report off. That will create a chart on the reporting canvas for you using Power BIâ&#x20AC;&#x2122;s best guess at the visualization style and also select one of the measures that you will report off.

- 17 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

If you click on the canvas so that you deselect the first chart then you can repeat the process and create new reports.

- 18 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

If you click on the chart type icon within the top-right of the chart then you will also see all of the different ways that you can visualize the data.

- 19 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

Just by changing the visualization you get as completely different insight into all of the data that you are looking at. For example, you can change a column chart into Tree Map to see the fill spread of the data.

- 20 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

If you want to modify the chart that is there, then you can just drag additional fields from the field explorer down into the layout panel. In this case we added the Customer dimension into the Tree Map to see if there were any customer groupings.

- 21 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

Something else that you can do is grab data and make it into a new filtered chart. For example, in the top chart we just grabbed the first column and dropped it into the canvas to create a new chart.

- 22 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

Then we repeated the process to bring in the two other pieces of data that we want to compare together in their stand-alone chart.

- 23 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

Pinning Your Favorite Charts To The Main Dashboard Once you have created your charts then you can do something that is even cooler, and that is to pin the ones that you are really interested in seeing to the main dashboard. That allows you to start building up a catalog of charts and reports that you want to monitor all of the time.

- 24 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

To do this, just click on the chart that you want to add to your favorites and then click on the Pushpin icon.

- 25 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

You will get a note in the header that says that the item has been pinned to the dashboard.

- 26 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

You can return to the dashboard just by clicking on the dashboard name within the header. Note: is this is a new report then you will also be asked to save the changes that you have made. This is very important to do because it allows the dashboard to return to this master page if the user wants more information. If you are asked to save the changes, click on the Save button.

- 27 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

Then you will be asked to give your report a name.

- 28 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

Just type in the name and then click on the Save button.

- 29 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

After the report has been saved you will return to the dashboard and you will see that the chart that you just created is now showing up there along with your data sources.

- 30 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

Rearranging Your Dashboard Once you start adding additional charts to your dashboard, you may want to tidy it up a little by repositioning all of the panels, and also resizing some of the tiles.

- 31 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

To do this, all you need to do is drag the tile to it’s new location.

- 32 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

You can also grab the size anchor in the bottom right of the chart and make the chart tiles biggerâ&#x20AC;Ś

- 33 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

Or smaller…

- 34 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

Also if you hover over the tile then you will see a Pencil icon is displayed.

- 35 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

If you click on the Pencil then you will see the default title and summary description.

- 36 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

If you want to add some different titles to your chart then go for it.

- 37 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

When you close out of the properties then you will see that your chart has a new heading.

- 38 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

You can re-arrange all of the tiles so that you have easy access to the data. How easy is that?

- 39 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

Installing The Power BI Designer You donâ&#x20AC;&#x2122;t have to just create dashboards through the web client though, hidden within the new Power BI Portal is a link that allows you to download a standalone Power BI Designer that you can install on you local machine, and build even better dashboards and charts, and also access more information including local data and web sites. All you need to do is ask for it.

- 40 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

To install the desktop client, just click on the Gear icon in the top right of the Power BI Portal and you will see that there is a link there is a link there for the Power BI Designer. Just click on it. That will download the install kit for the designer and load it for you.

- 41 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

After the Designer has been installed you will be taken straight to the Power BI Dashboard designer. That is way too convenient.

- 42 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

Adding Data To Your Dashboard Once you have the Power BI Designer client installed then you can start creating even better dashboards using even more data sources than were available through the web client. And to start off you need to add some data for the dashboard to use.

- 43 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

To do this click on the Get Data button within the Data group of the Home ribbon bar. When the dropdown list is displayed you will see a list of the most common data sources that you may want to populate for your dashboard. But these are not all of the data sources. Click on the More button at the bottom of the form.

- 44 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

That will open up the Get Data list and you will see that there are a lot of different options.

- 45 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

If you click on the File filter you will see that can get data from the file system in a number of different ways and formats.

- 46 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

If you filter the view to the Database data sources then you will see that you can get data from a whole slew of different databases â&#x20AC;&#x201C; not just the normal suspects from Microsoft.

- 47 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

If you filter the list to the Azure data sources then there are a lot of data sources that you could use including the Azure Marketplace.

- 48 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

And to round out the available data sources, click on the Other filter. These are the more interesting options because you will notice that you can get data from SharePoint, Odata Feeds, Active Directory, Exchange and even Facebook.

- 49 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

The best place to get data for Dynamics AX though is from the cubes, so click on the Database filter and select the SQL Server Analysis Services Database option.

- 50 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

You will then be asked which Analysis Services server you want to connect to.

- 51 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

Type in the server name and then click on the OK button.

- 52 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

When the Authentication options show up, just select the Windows option and then click on the Save button.

- 53 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

Next the data Navigator will be displayed, and you will see all of the available OLAP Cubes that are available for you to report off.

- 54 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

If you open us the default set of cubes you will see all 18+ cubes that are delivered with Dynamics AX.

- 55 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

Expand out the Sales Cube so that we can see all of the measures and dimensions that are available within the standard cube.

- 56 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

If you expand out the measure group Customer Invoice Lines then you will see that there are a lot of standard measures that we can report off.

- 57 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

Select the Customer Invoice Line Amount â&#x20AC;&#x201C; Accounting Currency measure. Then click on the Load button to return to the dashboard designer.

- 58 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

When you return back to the Query page you will now see that all of the invoice line data has been aggregated up into one value, and you will see the Power Query steps are listed on the right hand panel.

- 59 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

Adding Additional Fields To A Query We want to see a little more information though so that we have a little more information to work with within our dashboards.

- 60 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

To do this click on the Gear icon to the right of the Added Items step to modify the returned columns. That will open up the Sales Cube again with all of the measures and dimensions for us to choose from.

- 61 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

First start off by expanding out the Company dimension group.

- 62 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

Then select the Company Name dimension so that we will be able to slice, dice, and filter by the company.

- 63 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

Then expand the Customer dimension group.

- 64 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

Select the Customer Group Name, Country/Region, and the Customer – Name dimensions. Note: We choose the Name dimensions so that we don’t end up having to decipher the customer and group codes.

- 65 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

Scroll further down within the Customers group and also select the Sales Group, State, and the ZIP/Postal Code fields to give us more options for slicing and dicing.

- 66 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

Next expand the Date – Date dimension group.

- 67 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

Select the Month Of Year and also the Year dimensions.

- 68 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

Then expand the Released Products dimension group so that we can add some product dimensions to the query.

- 69 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

Select both the Item Group, and the Product Number dimensions.

- 70 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

Finally, expand out the Worker dimension group.

- 71 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

From there select the Worker Name dimension so that we can analyze who is placing the orders. After you are done, click on the OK button to re-load the query with the new data.

- 72 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

When you return to the query and we will see that there is a lot more data now for us to play with.

- 73 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

Filtering The Query Data If you donâ&#x20AC;&#x2122;t want all of the data to be used within your dashboard and save you from having to filter out each individual dashboard as you create it then you can add a Filter step to the Power Query.

- 74 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

To do this, just click on the field heading that you want to filter out (in this case the Company Name column and you will see the filter dialog is displayed.

- 75 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

You can quickly deselect all of the values, and then select just the Company that you want to filter the data to. When you return to the view you will only see your filtered data.

- 76 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

- 77 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

Splitting Fields To Make Data Easier To Report Off Sometimes the data isnâ&#x20AC;&#x2122;t in quite the format that you really want it. For example, by default, the Year dimension has the Calendar prefix. Although it looks nice, you really just want the year value. To help with that you can use the Split function to break up the field into two parts based on a delimiter.

- 78 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

All you need to do is right-mouse-click on the Year column select the Split Column menu item and then select the By Delimiter menu item.

- 79 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

This will open up a Split dialog box.

- 80 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

Click on the Select or Enter Delimiter dropdown box, and select the Space option. Then click on the OK button.

- 81 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

When you return to the query you will see that the Year column has been split into two with all the Years stored in the second column.

- 82 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

Lets do the same for the Month by right-mouse-clicking on the Month column, selecting the Split Columns menu item and then choosing the By Delimiter menu item.

- 83 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

Select the Delimiter type of Space and then click on the OK button.

- 84 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

Now the Month will be separated out as well.

- 85 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

Deleting Unwanted Columns As you start manipulating the data, and splitting columns you will probably end up with a number of columns that you really donâ&#x20AC;&#x2122;t need. If that happens, then just delete them.

- 86 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

To do this, right-mouse-click on the column that you don’t want (in this example it’s the first half of the Year column and then select the Remove menu item.

- 87 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

Now the column is gone.

- 88 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

Repeat the process for the Month column.

- 89 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

Now you only have one Month column. That’s a lot tidier.

- 90 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

Changing Column Data Types Power Query is smart, but sometimes it need just a little helping hand to make sure that the data is in the right format and treated properly. And an example of that is that you sometimes need to adjust the datatype of a column so that you will get the best sorting and grouping features.

- 91 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

For example, the Year column has a default datatype of Text, but it would be better if it were a number. So select the column and then click on the Data Type dropdown list within the Transform group of the Home ribbon bar and select the Decimal Number option.

- 92 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

Now your year will be treated like a number.

- 93 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

Creating New Calculated Columns As you build your Power Query you may find that you want to create a new column that is based off the data that is already in the query. Don’t fret – it’s easy to do. In our example since we have both the year and the month values, it would be nice to add them together so that we have a sequence that we can use in the dashboard charts that shows every month and every year sequentially. So that is a perfect opportunity to create a new custom column and formula.

- 94 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

To do this, click on the Add Custom Column button within the General group of the Add Column ribbon bar.

- 95 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

This will open up an Add Custom Column editor.

- 96 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

Change the name of the field to something more appropriate â&#x20AC;&#x201C; for this example we are renaming it to Period.

- 97 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

And then create the formula that you will use to build the field. When you are done, just click on the OK button.

- 98 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

When you return to the query you will see that you have a new Period column that is a concatenation of the year and month.

- 99 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

Loading the Query Into The Dashboard Project Now that we have finished designing our query we can change gears and start using it within the dashboards themselves as a data source. To start though we need to perform a full query and save it within the project.

- 100 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

To do this, simply switch to the Report view. That will evaluate the query and then load the fill dataset into the project. This may take a couple of secondsâ&#x20AC;Ś

- 101 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

When all the data has been loaded you will see that your query is not shown on the right hand side of the designer. Now we can start doing the fun stuff.

- 102 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

Creating A Dashboard Chart Now we can start adding our charts and graphs to the dashboard and we do that exactly the same was as we did within the web client.

- 103 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

All we need to do is select the Measure that we want to report off – in this case it’s the Customer Invoice Line Amount. That will create the first chart on the page for us.

- 104 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

To turn this into something that is a little more informative, choose a dimension that you want to split the chart values on â&#x20AC;&#x201C; and for that we will use the Period dimension. Now we have a chart.

- 105 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

Changing The Sort Order On Charts By default the charts are sorted from highest to lowest, but that doesnâ&#x20AC;&#x2122;t mean that is how you have to see it. In our example we want to sort by the Period so that we can see the trend over time.

- 106 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

To change the sort, start of my clicking on the heading of the chart and then clicking on the Sort By dropdown. That will allow you to select the Period field as the sort field.

- 107 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

That will get the sorting nearly right, except it is being showing in descending order and we really want it to be in ascending order. If you click on the heading again you will be able to click on the sort order icon to switch the sort mode.

- 108 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

Now the report is sorted in the right way.

- 109 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

Changing The Report Type There are a lot of different types of report visualizations that you can use, so you are not stuck with the option that Power BI selects for you.

- 110 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

If you click on the Visualize dropdown list within the Home ribbon bar you will see that there are a boat load of different ways that you can view the data. To show the sales chart in something a little more appropriate then select the Line chart.

- 111 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

That looks better.

- 112 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

Viewing Your Data With Gauges You donâ&#x20AC;&#x2122;t have to just look at your data within the with all of the generic views like bar, chart or line. There are a lot of other ways that you can view your data. One example is the Gauge view.

- 113 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

Start off by selecting the data that you want to view â&#x20AC;&#x201C; in this example we will choose the Customer Invoice Line Amount measure and the Customer Group dimension. This creates a simple column chart for us.

- 114 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

Just click on the Visualizations dropdown in the Home ribbon bar and select the Gauge visualization.

- 115 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

Using Vertical Multiples To Split Out Data One of the problems with the Gauge visualization though is that it only has one value, and there are no dimensions associated with it. There is a trick though that allows you to create multiple charts based on a dimension and that is through the use of the Vertical Multiples.

- 116 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

All you need to do is drag a dimension down into the Vertical Multiples area of the chart configuration. If you drop the Customer Group dimension there you will end up with the system creating a chart for each of the values.

- 117 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

Using Advanced Queries To Create Parameters For Charts You may have noticed with the Gauge components that all of the values are showing as average even though the values are all different. That is because there are no Minimums, Maximums, or Target values defined for the gauge. The problem is that the all of these values need to be populated from a query, and you canâ&#x20AC;&#x2122;t type them in. There is a trick that you can use to create these values, and that is by creating your own query table with your own data using the Advanced Query editor.

- 118 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

To start off, return to the Query panel.

- 119 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

Then click on the Get Data menu button within the Home tab and select the Blank Query option.

- 120 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

This will open up a new query for you, and you just need to design the data.

- 121 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

Click on the Advanced Editor button within the Show group of the View ribbon bar.

- 122 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

This will open up an Advanced Editor dialog box. It’s time to learn a new language called M.

- 123 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

To start off we will create a simple query with one column. Type in the following code into the Query Editor and press the Done button. let

Source = #table({"Min"}, { {10000000} }), in Source

- 124 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

That will create a table for you. That is cool.

- 125 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

Click on the Advanced Editor again to open up the query and change extend out the query to add two more fields. let

Source = #table({"Min","Max","Target"}, { {10000000,100000000,25000000} }), in Source

- 126 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

Now you have three columns.

- 127 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

Now change the datatype on the fields from Text to Whole Numbers.

- 128 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

Finally, you can change the Name property so that you donâ&#x20AC;&#x2122;t have to remember what the query is for.

- 130 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

Now return to the Report and you will see that your Quotas query is available to be used.

- 132 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

All you need to do is drag the Min, Max, and Target fields down into the Guages optional parameters.

- 133 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

Now your Gauges will look a little more informative. How cool is that.

- 134 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

Finishing Off The Dashboard Now that you have the basics of the dashboard design down pat you can add a few more charts to your dashboard page.

- 135 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

Start off by adding a view that shows the sales by state and then change the visualization to a map.

- 136 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

And then add two more views that show the sales by product and customer so that you can see who and what is the most profitable.

- 137 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

Filtering The Dashboard By Filtering Charts Now that you have the dashboard you can start analyzing the relationships between all of the charts.

- 138 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

All you need to do is click on one of the data elements that you are interested in and all of the other charts and gauges will filter themselves out.

- 139 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

Creating Additional Pages For Your Dashboard As you add more and more charts and reports to your dashboard you will quickly find that you are running out of real estate. Donâ&#x20AC;&#x2122;t worry though you can create additional pages for your dashboards with a click of a button and then flip through them at your leisure.

- 140 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

To do that, just click on the New Page button within the Report group of the Home ribbon bar. On the left hand panel you will notice that you now have a new blank page that you can start adding new reports to.

- 141 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

Creating Queries From Web Pages Up until now we have only been showing how you can get data from Dynamics AX, but the Power part of Power BI is the ability to grab data from multiple data sources and use them within your dashboards. One feature that is super cool is the ability to give Power BI a website and it will analyze the structure, find any tabular data that is in there and turn it into a query.

- 142 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

One common query that you may want to pull into your dashboards are currency exchange rates. You can quickly find the latest rates on the web, but the trick is to get them into your reports.

- 143 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

To do this, just switch to the Query editor and click on the Get Data menu button and then select the Web query type.

- 144 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

This will open up a dialog box asking you for the URL that you want to use for the query.

- 145 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

Just paste in the URL for the website with the currency rates on it and then click the OK button.

- 146 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

That will make the query editor process the website and it will return back with anything that looks like a table within a Navigator window. All you have to do is select the table that you like and then click on the Load button.

- 147 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

When you return back to the Report view you will see that you now have a new query for the currency rates.

- 148 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

You can add the table fields to the dashboard and now you will have a panel that shows you all of the currency conversion rates, refreshed directly from the web.

- 149 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

You can even change the Visualization to a card view to make it look prettier.

- 150 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

Creating Queries Using Static Data From Excel Another data source that you can use for your queries are Excel spreadsheets. Everyone has them laying around that they reference KPIâ&#x20AC;&#x2122;s from or historical data, so rather than lock them away so that only one person is able to use them, why not add them to your data model.

- 151 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

Start off this example by clicking on the New Page button within the Report group of the Home ribbon bar to create a new page.

- 152 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

Then click on the Get Data button within the Data group of the Home ribbon bar. This as another way that you can pull up the list of data sources. Now select the Excel data source and click on the Connect button.

- 153 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

That will open up a file explorer window where you can find your Excel workbook and then click on the Open button to use it for the source of the query.

- 154 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

When the Navigator window is displayed, you will see all of the sheets are available for selection and you will be able to preview the data that is being returned. Just select the table that you want to use and then click on the Load button.

- 155 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

When you return to the Report panel you will be able to see that the Excel data has been converted into a query and you are able to select any of the fields that were part of that worksheet.

- 156 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

You can build up the dashboard exactly the same way as you could with the cube data, and even include reports from multiple queries into the same panel.

- 157 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

Saving Your Dashboard Project Once you are done designing your dashboards, it is probably a good idea just to save them away so that you donâ&#x20AC;&#x2122;t have to recreate the wheel. Also this will allow you to use them in the following step.

- 158 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

All you need to do is click on the File menu and select the Save As menu item.

- 159 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

When the Save As dialog box is displayed, give your project a name and then click the Save button.

- 160 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

Importing Desktop Projects Into Power BI Within Office 365 One of the reasons why to want to save your BI projects is so that you can then import them into Office 365 so that everyone has access to them.

- 161 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

To di this, open up your Power BI Portal and click on the Get Data link at the top of the left hand panel.

- 162 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

When the Get Data page is displayed, select the Power BI Designer File as the source and then click on the Connect button.

- 163 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

When the file path is displayed, click on the Browse button.

- 164 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

That will open up the File Explorer and you will be able to navigate to where you put your project file and then click the Open button.

- 165 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

That will take you back to your dashboard page, and you will notice that there is a new data source that the site is loading. Once it has been loaded, all you need to do in order to access it is click on the tile.

- 166 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

That will take you to the same dashboard that you just built except it will be completely web based.

- 167 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

Clicking on the elements filters out the data just like you did in the desktop client. How cool is that?

- 168 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

Creating New Dashboards Within the Power BI Portal you can create multiple dashboards that hold different charts sourced from different reports that you have created. This is a useful way to segregate out information and also create simple dashboards that you share with different people.

- 169 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

To do this just click on the + sign to the right of the Dashboards header within the left hand menu.

- 170 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

That will create a new Dashboard placeholder for you.

- 171 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

All you need to do is type in a name and then press Enter and you have a new blank dashboard.

- 172 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

Populating Dashboards With Report Content Once you have a clean dashboard itâ&#x20AC;&#x2122;s time to start adding charts to it.

- 173 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

To do this, open up the Report that contains the chart that you want to pin to the dashboard and then click on the Pushpin icon.

- 174 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

You will get a notice that the chart has been added to the dashboard.

- 175 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

When you return to the dashboard you will see a tiled version of the chart.

- 176 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

If you go to another report (that doesnâ&#x20AC;&#x2122;t even have to have the same query foundation) then you can pin any of those charts to the dashboard as well.

- 177 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

When you return to the dashboard you will see that all of the charts have been added as tiles.

- 178 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

All that is left to do is to re-arrange and resize the tiles so that they look nice.

- 179 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

Sharing Your Dashboards With Others Once you create your dashboards, you may want to set them free and let others bask in the glory of the data. Power BI makes this process as simple as pie as well.

- 180 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

All you need to do is click on the Sharing icon to the right of the dashboard name.

- 181 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

That will open up the sharing panel.

- 182 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

Just type in the name of the person (within your organization) that you want to have access to the dashboard and then click the Share button.

- 183 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

They will receive an e-mail inviting them to the dashboard and then they just click on the link and log into Power BI with their credentials. How easy is that?

- 184 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

WORKED EXAMPLE: Creating a Consolidated Financials Overview Dashboard There is no better way to learn how to use the Power BI Designer than to actually get your hands dirty and set up a dashboard from scratch. In this worked example we will allow you to do that by walking you through all of the steps involved in creating a consolidated dashboard that combines receivable, payable and sales data directly from Dynamics AX, using the standard reporting cubes as the source.

- 185 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

So lets get started by opening up the Power BI Designer.

- 186 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

Click on the Get Data menu button within the Data group of the Home ribbon bar and then select the More option.

- 187 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

When the Get Data dialog box is displayed, select the SQL Server Analysis Services Database option and click on the Connect button.

- 188 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

When the dialog box for the server is displayed, type in the server name (or . If you are on the same server) and then click the OK button.

- 190 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

When the data Navigator is displayed, expand out the Dynamics AX Initial database and expand the Accounts Payable cube.

- 192 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

From the measures select the Accounts Payable Open Amount, Accounts Payable Total, Accounts Payable Amount, and also the Accounts Payable Settled Amount measures.

- 194 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

From within the Company dimension group, select the Company Name dimension.

- 196 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

From within the Due Date dimension group, select the Date and Year dimensions.

- 198 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

Then from within the Vendors dimension group, select the Terms Of Payment, Group Name, and Vendor Name dimensions. When you have done that click on the Load button.

- 201 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

That will take you to the Report page and you will see all of your dimensions, only they look a little long and cryptic.

- 202 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

Click on the Query page and you will be able to see all of the Columns.

- 203 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

Double click on the Company – Company Name column and rename it to Company.

- 204 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

Repeat the process for all of the other fields using these headings: Due Date Year Payment Terms Vendor Group Vendor Open Payable Total Payable Payable Settled Payable

- 206 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

Finally, select the Due Date column and click on the Data Type field within the Transform group of the Home ribbon bat and change it from Text to Date.

- 207 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

Click on the Get Data menu button again within the Data group of the Home ribbon bar and then select the More option.

- 209 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

When the Get Data dialog box is displayed, select the SQL Server Analysis Services Database option and click on the Connect button.

- 210 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

When the dialog box for the server is displayed, type in the server name (or . If you are on the same server) and then click the OK button.

- 211 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

When the data Navigator is displayed, expand out the Dynamics AX Initial database and expand the Accounts Receivable cube.

- 212 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

From the measures select the Accounts Receivable Open Amount, Accounts Receivable Total, Accounts Receivable Amount, and also the Accounts Receivable Settled Amount measures.

- 214 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

From within the Company dimension group, select the Company Name dimension.

- 216 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

Then from within the Customers dimension group, select the Terms Of Payment, Customer Group Name, and Customer Name dimensions.

- 218 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

From within the Due Date dimension group, select the Date and Year dimensions. When you have done that click on the Load button.

- 221 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

Return to the Query page and you will see your new query.

- 223 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

Rename the field headings to be: Company Due Date Year Payment Terms Customer Group Customer Open Receivable Total Receivable Receivable Settled Receivable Finally, select the Due Date column and click on the Data Type field within the Transform group of the Home ribbon bat and change it from Text to Date. - 224 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

Then click on the Get Data menu button one last time within the Data group of the Home ribbon bar and then select the More option.

- 225 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

When the Get Data dialog box is displayed, select the SQL Server Analysis Services Database option and click on the Connect button.

- 226 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

When the dialog box for the server is displayed, type in the server name (or . If you are on the same server) and then click the OK button.

- 227 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

When the data Navigator is displayed, expand out the Dynamics AX Initial database and expand the Sales cube.

- 228 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

From the measures select the Customer Invoice Line Amount measure.

- 230 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

From within the Company dimension group, select the Company Name dimension.

- 232 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

Then from within the Customers dimension group, select the City, Customer Group Name, Customer Name, Sales District, Sales Group, State, and ZIP/Postal Code dimensions.

- 234 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

From within the Due Date dimension group, select the Date and Year dimensions.

- 237 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

Then from within the Released Products dimension group, select the Item Group, Product Number, and Retail Category dimensions. When you have done that click on the Load button.

- 240 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

Rename the field headings to be: Company City Customer Group Customer Sales District Postal Code Date Year Item Group Retail Category Invoice Amount

Finally, select the Due Date column and click on the Data Type field within the Transform group of the Home ribbon bat and change it from Text to Date. - 242 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

Now click on the File menu and select the Save As menu item.

- 243 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

When the Save As Dialog box is displayed, type in a name for the project (Consolidated) and then click the Save button. When you have saved the project, exit from the Designer.

- 244 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

Now return to the Power BI Portal.

- 245 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

Click on the + icon to the right of the Dashboards group.

- 246 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

Type in a name for the new dashboard (Consolidated Financials) and press enter.

- 247 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

Then click on the Get Data link and when the Get Data page is displayed, select the Power BI Designer File as your source data type. Then click the Connect button.

- 248 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

When the file explorer is displayed, find your project file and then click the Open button.

- 249 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

When you return back to the Get Data form, click on the Connect button.

- 250 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

You will get a notice that Power BI is importing the data. Just wait a couple of seconds.

- 251 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

When the data is imported you will see it as a data source within your dashboard.

- 252 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

Now lets build our data using the Q&A feature. To do this type in customer group Inside the dashboard you will see a list of all of the customer groups from the data source.

- 253 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

Now change the query to: customer group receivable The chart will change to show all of the receivables by customer group.

- 254 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

Make one last adjustment and change the query to: customer group receivable as pie The chart will change to show all of the receivables by customer group as a pie chart.

- 255 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

Click on the Pushpin icon to add this view to the dashboard.

- 256 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

Change the query to: vendor group payable as pie The chart will change to show all of the payables by vendor group as a pie chart. Click on the Pushpin icon to add this view to the dashboard.

- 257 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

Change the query to: customer group invoice as pie The chart will change to show all of the invoice amounts by customer group as a pie chart. Click on the Pushpin icon to add this view to the dashboard.

- 258 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

Change the query one last time to: Item group invoice as pie The chart will change to show all of the invoice amounts by item group as a pie chart. Click on the Pushpin icon to add this view to the dashboard.

- 259 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

Click on the Consolidated Financials title to return to the dashboard and you will see that all four of your charts have been added.

- 260 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

You can re-arrange the charts so that you can see them all just by dragging them around. Now we will create a couple of charts a more traditional way by clicking on the Consolidated.pbix tile which is the link to the source data.

- 261 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

When the report designer is shown you will see that all of your cube data is shows within the field explorer.

- 263 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

Scroll down to the Sales Cube group and select the Invoice Amount measure and the Item Group dimension.

- 265 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

You will notice that there is a N/A column that is skewing the data, so switch the view in the right hand panel from Fields to Filters and you will see that both fields in the report are being shown.

- 266 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

Click on the All button under the Item group and expand out the values.

- 267 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

Click on the All box to select all of the item groups.

- 268 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

Then scroll down to the bottom and uncheck the NA item group to filter out that data.

- 269 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

Now repeat the process, but this time create the graph for the Customer Groups.

- 270 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

Then for each of the charts, click on the Pushpin to add it to the dashboard.

- 271 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

Return to the main dashboard, and when you are asked to save the data, click on the Yes button.

- 272 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

Now you will see that you have some more tiles on your dashboard.

- 273 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

Letâ&#x20AC;&#x2122;s add a few more metrics, but this time we just want the totals so type into the Q&A box total open receivable

When the total is shown, click on the Pushpin icon to add this view to the dashboard.

- 275 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

Then type total open payable When the total is shown, click on the Pushpin icon to add this view to the dashboard.

- 276 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

Then type total invoice When the total is shown, click on the Pushpin icon to add this view to the dashboard.

- 277 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

Then use the dashboard designer to create some summaries of the Invoice Amount by Customer Group and Item Group by state so that you get some maps. For both of these, click on the Pushpin to add them to the dashboard.

- 280 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

Return to the main dashboard, and when you are asked to save the data, click on the Yes button.

- 281 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

Now you can resize all of the tiles and you end up with a very cool and useful dashboard for all of the finance people to use. How easy is that?

- 282 -

USING THE POWER BI DESIGNER TO BUILD DYNAMICS AX DASHBOARDS

Summary The new Power BI Portal and the Power BI Designer are two great tools that you must take a look at. Rather than having to create your queries within Excel and then having to massage the data in PowerPivot, you have a single place where you can start creating your own reports in a very intuitive way. Hopefully this overview has given you a good idea of the capabilities and you are chomping at the bit to start using them. So what are you waiting for? Do it already!

- 283 -