In the previous recipe we saw how Power BI creates hierarchies based on date fields, collecting aggregated data on several levels from year to day. Date is not the only possible way to build a scalable report. It is possible to build a hierarchy based on any data, as long as you can provide a link from one level to the next.
In the next recipe, we will create a hierarchy based on geographical data from customer information.
Unfold the list of tables in the database
Demo Database NAV (9-0)
, and select two tables from the list:CRONUS International Ltd_$Customer
andCRONUS International Ltd_$Detailed Cust_ Ledg_ Entry
. After selecting the tables, click Edit. Table queries will be opened in the query editor.Enter the SQL Server name where the NAV database is located (the default name is
localhost\navdemo
), then click OK.Run Power BI, select the Get Data action in the External Data ribbon group, and choose SQL Server as the data provider type...