The use of Power BI has exploded across rail infrastructure project management teams. With most users being new to business intelligence and data visualistion tools, it's important to focus on the key ways to get Power BI working for you.
Here are 7 tips to get Power BI working for you:
Tip 1 - Calendar/Date table
Generate your calendar table with M Script or DAX.
Use Relative Period fields => Dynamic Filter Reports over Time.
Tip 2 - Optimize your model size
Focus on columns with high cardinality.
Use Status Bar, DMV’s or tools like VertiPaq Analyser.
Our suggested approaches:
- Remove columns you don’t need (ex: Transaction Number) / Find alternate ways (ex: separate query)
- Separate query for things like “Transaction Count”
- Split column for less distincts (ex: DateTime into Date + Hour)
Tip 3 - Identify the cause of a slow report
- Create an empty page
- Open Dax Studio and start capturing
Tip 4 - Change defaults on Data Load
Make Power BI Desktop auto detect your datatypes for you. To change this setting:
- Click on the File button
- In the drop down menu, Click on the Options and Settings
- In the menu on the right Click the button labeled Options
- Under the Global section in the Options menu Select the item labeled Data Load
- Change the Type Detection for loading data
Tip 5 - Use Keyboard Shortcuts on DAX Editor
CTRL + SHIFT + L Find and Replace ALL highlighted text
CTRL + D Find and replace highlighted text
SHIFT + ENTER New Line with Indent
CTRL + ALT + UP/Down Enter multiple lines at once
CTRL + SPACE Activate Intellisense
CTRL + K + C/U Comment/Uncomment multiple lines
ALT + UP/Down Move lines up/down
CTRL + G Go to line number
Tip 6 - Combine all measures in one table
Sometimes so many measures are created that it is difficult to find them. So, in this case you can organize all the measures in a table which is dedicated only to measures.
- Create a new table (doesn’t matter even if the column values are empty).
- Now create one measure in the table and delete the empty column.
- All your measures can be created in the same table now.
- Here you can easily find your measures.
Tip 7 - Change date hierarchy on axis using bookmarks
Add hierarchical elements (day, month, quarter, year) into the axis and achieve drilldown using the drilldown option.
However, the challenge is when there are too many charts and you cannot manually use the drilldown option for each chart.
This is where you can make use of bookmarks.
- At first, you create charts with one hierarchical element (either day/month/quarter/year) in a page. All you need to do next is to just duplicate the page and change the hierarchical element in the axis. Suppose you created charts using ‘months’, change it to ‘year’.
- The next step to be followed is to bookmark these pages and assign them to the buttons.
- Place the buttons on the main page.
- Remember to assign the bookmarks to the buttons of both pages and sync the slicers.
A Growing Online Community
Power BI boasts over 5 million users across more than 200,000 organisations. Its online community is used to share tips and ideas, often specific to an industry itself. Use it to find pre-made dashboards that can monitor your rail infrastructure construction projects, explore what the software can do and improve your level of insight.
Raildiary’s market-leading reporting software can be used as a single source of data for Power BI. We automat the collection of data and integrate with the Power BI platform to create bespoke dashboards specific to your company, project and individual job role, which you can then share with others.