Just a few bits that are useful to me when creating apps
For example, if you have the following table:
Search Engine Address
And you bind it to a gallery. In the gallery template, you add a text box control, with the following properties:
A better search box is as follows:-
Search( PartyContact, TextSearchBox1.Text, “PartyName”)
SortByColumns(Search( PartyContact, TextSearchBox1.Text, “PartyName”),”PartyName”,Ascending)
Card controls – for putting into view only mode DisplayMode.View
Formattting dates for UK
Text(DatePicker1.SelectedDate,”[$-en-US] dddd dd mmm yyyy”)
Details on the patch function
Note to myself Code required for embedding PowerApps into a Power BI dashboard
<iframe width=”98%” height=”98%” src=”https://web.powerapps.com/webplayer/app?appId=%2fproviders%2fMicrosoft.PowerApps%2fapps%2f
XXXXXXXXXXXXXXXXXXXXXXXXXXXXX- recover this from your powerapps details pane
By far the clearest video on this topic that I’ve seen. Well done Daniel!
So the November update is out. Sadly, it doesn’t have the filters across pages which will prove an extremely good way of decluttering a dahsboard.
Here’s a summary of the updates, and what I think of them.
Here’s the complete list of November updates:
- Rule-based conditional formatting for table and matrix
- This is likely to be very useful for a wide range of clients. It’s the sort of thing you would do in excel from time to time, however in a BI project colour based analytics are very compelling.
- Cell alignment for table and matrix
- Very useful, particularly when trying to produce financial reports where line items and subtotals really need to be offset from each other to create a better visual experience
- Control visual ordering through the selection pane
- Struggling to see where I would need this one. It’s the equivalent of sending objects to the back/front in PowerPoint
- Lock objects on your report
- Very very handy, one of those things that it’s surprising they hadn’t thought of until now. Prior to this it was very easy, particularly in dashboard view to move visuals around by accident
- Esri Plus Subscription is available for ArcGIS Maps for Power BI
- Very nice, but not something I particularly have a need for. I suspect that this will be incredibly useful for some clients
- Report options for slow data sources
- Quite technical, but essentially means that you fire less queries at your models, or none at all depending upon how you configure them. Bear in mind the real way of addressing speed issues is to house it in Azure Analysis Services
- Filtering performance improvements
- This one passed me by, I’m sure it’s very useful!
- Recently released custom visuals
- Image Timeline
- Really nice visual, I’m a big fan of images in reports and the application of PBI in the education space
- Social network graph
- Interesting, personally I wouldn’t use it for this, however it is a good way of expressing the hierarchy within an organisation
- Venn diagram by MAQ Software
- Another one that passed me by
- HTML viewer
- Quite technical, it’s extremely clever that they have managed to come up with this but I suspect that the use will be relatively narrow.
- Image Timeline
- Cell-level formatting for multidimensional AS models for multi-row card
- I would suggest that this is for multinational clients with highly bespoke requirements.
- Impala connector – support for Windows Authentication
- No relevant to anything I do
- Add Column From Examples improvements
- I’ve never found add column from examples to be any use to me. You’re better off learning how to build custom columns.
For a summary of the major updates, you can watch the following video:
Great vid from MS Ignite showing how PowerApps, PowerBI, Sharepoint all work together.
Great vid on Power BI Excel Better together. I’ve not done a lot with Excel recently, but it really is a great mashup and forecasting tool.
Some guidance on building effective dashboards in Power BI. There is only a loose order to this guide, but if you’d like me to expand on anything just let me know.
Ensure that your dashboard has some form of ‘story’ to it
Roughly speaking this would mean that the earlier tabs are less detailed and might contain more kpis whilst the later ones allow for further investigation of unusual areas.
Before you start understand how deeply you would like to go in terms of the lowest grain of your model. What is the most detailed level that you would like to go to? In my case I often decide that I would like to understand the deepest grain, however that does not need to be at the centre of your fact table. Careful use of the Summarize function can often mean that you can have the best of both worlds. i.e. a Summarised table using the summarize function as the central fact table and a star schema to a detailed fact table.
Combine Visuals and Tables
Another personal point, but I feel more comfortable presenting the information in this way as users get a very strong feeling as to the integrity of the model that you have built. This is particularly the case when they interact with the model by clicking on a given chart, whether it is your 0-30 day debt, your gender split or profit by business unit. Ultimately this is likely to lead to a better take up of your product.
Let the Data Express Itself
Power BI is a new product and a new type of tool for many people and very often you will find yourself with a set of requirements prepared by that have predefined thoughts about what the data must be telling them. Quite often they are right, but don’t be afraid to see if there are simple patterns that can be exposed just by dragging a few fields into a visual.
Be a Student
You can be quite successful with only a little knowledge of Power BI. However, don’t expect to by truly and deeply successful as an analyst unless you have a strong foundation of PowerQuery and DAX skills. Competence with these (PowerQuery to extract transform and load your data into a set of tables, and DAX to perform a range of calculations over them) is gamechanging and transforms your ability to deliver value with the product.
This is an old but still useful course on using Power BI https://courses.edx.org/courses/course-v1:Microsoft+DAT207x+6T2016/course/
I’m a big fan of simple bar and line charts, combined with a nicely formatted table, but some of the custom visuals can be extremely effective such as the hierarchy slicer shown here:- https://store.office.com/en-us/app.aspx?assetid=WA104380820&ui=en-US&rs=en-US&ad=US&appredirect=false
The ribbon slicer is also a very neat way of expressing positions over time (say in a football league, but any other league for that matter)
Play, Play, Play
We all learned as children through play and this tool should be no different. Personally, it’s very difficult to learn this topic in an abstract fashion. It is far better to take something you understand and visualise that as the errors are more obvious.
The link below is to an attempt to make Power BI more audio visual
The following is my exploration of how to perform vehicle lease calculations. Whilst it’s not the most interesting of topics I’m aware that from an accounting perspective this simplifies what are relatively complex calculations. On the face of the report it appears straightforward however under the bonnet this is a very powerful demonstration of how to use functions to power up your analytics. I’ll spare you the details on this occasion.
This report here was created when we were looking for a puppy. I still enjoy just how well focussed it was on our needs at the time.
Every ‘playtime’ report that I have created has moved me several steps on, partly because I played so many stakeholder roles throughout the process. I’ve also been able overcome the technically obstacles along the way and I haven’t been hampered by waiting on anything in order to realise my visions.
First Attempt In Learning
You won’t achieve success without the many micro fails along the way. They can be extremely frustrating, especially when you know you’re about 30 keystrokes from success.
My stand out story on this was very much around dynamic row level security which was uber essential for our reporting solution. It took me a number of days with the help of a colleague, but we got there in the end (and it was about 300 keystrokes!)
Don’t try and get Power BI to do things it’s not built for
Power BI is not made for printing unless the reports are heavily tailored, which generally compromises the overall effectiveness of the report. Custom visuals generally do not print at all.
Also, don’t invent and propose visuals that Power BI doesn’t produce – instead work with the visuals and custom visuals that are already available. There are many examples where competitor products are stronger in a particular area, so don’t necessarily try and compete with them in these areas head to head.
Power Query/M Competence
Power Query/M – A little competence in this area can go a long way. The most important features are as follows:-
- Column Splitting
- Assigning data types to columns (Date, Datetime, number)
- Creating conditional columns
- Merging with other queries
- Reviewing the formula bar – it’s the first step to understanding the language
- Navigating through the steps
- Find and replace. And finally….
- Unpivot – possibly the most important feature as this is reshapes your data to a format that is generally acceptable to a data model
The level beyond this involves writing efficient queries that refer back to earlier steps and writing functions, but that is for another day.
Summarize() those tables
Once you have landed your tables into the model summarize can enable taking a very long table to be aggregated in a way that make it possible for you to ensure that you do not end up with tables containing many to many relationships. A combination of summarize filter and union can be extremely effective in this regard.
Here’s a good link for this https://www.youtube.com/watch?v=-Ola264bKXk
This is one of my videos on the same subject covering Bradford Factor https://www.youtube.com/watch?v=U-B_0DuZlX0&t=1s
Create dummy measures table
Measures are very poor companions to calculated columns. Calculated columns live in the table to which the relate, but measures can exist anywhere. When calculated columns and measures appear in the same table it is quite frankly confusing. It is far simple to house all your measures in one empty home table and leave it at that.
The process for this is as follows:- Home Tab, EnterData, Rename Table from ‘Table1’ to Measure. From now on put all your measures in here.
Design your model backwards
To begin with you may have a clearer idea as to what you want your visuals to look like than the data model that is required to support them. With that in mind I have found it can be helpful to build a prototype that focuses on the visuals plus the worlds most basic table to support this. Typically, it’s better to put the table into excel as you can build and test, then build and test iteratively. A few cycles of this and the shape of the underlying data model that you require will become more apparent.
Housing your detailed facts should almost always be in a database, but you will inevitably come across those situations where extra tables that assist with enhancing your model are required.
First prize is to have your data in a data warehouse, ideally in the cloud, however these are often difficult to updated.
Second prize is to house your data in a sharepoint list as it combines well with Power BI, in particular from a refresh perspective. It can be a great way of housing permission related items as you get a very good audit trail at a line item level and you do have the option of making use of Office 365 in the form of Flow and PowerApps. Generally speaking it is good where the lines of data are entered manually rather than in bulk.
Third prize is to put your ancilliary data into excel in a sharepoint site dedicated to your project. The problem with excel in these circumstances is that it is less easy to lock down the shape of the spreadsheet. Nevertheless it is an approach that I have taken and would recommend, just make sure you limit the number of authors and keep yourself to just 1 sheet.
Beyond that you can create a table which is effectively a Json file that sits within the PBIX file – you can go up to roughly 500 rows with this approach. It’s useful but bear in mind that if you do want to update it you will need to pull it down from the web location amend it and reupload it again. The process to create this is Home->EnterDate then just start entering data, or better still just paste it in.
Finally, you can put your data into excel on your server and refresh using the Power BI Gateway.
Use the PBI Gallery for getting ideas
The Power BI Gallery contains very useful ideas from the perspective of presentation. I would recommend that you take a look around as quite often there are clever uses of the tools available. See link below:-
Put borders on all your visuals
It’s a personal point but it is my preference, however I think with further advancement I may relax on this somewhat!
Reports seem more effective than dashboards
So don’t focus on dashboards. The reports themselves are dashboard – I don’t find that the dashboards are that useful and only act as a portal to the report itself. I think the main reason for this is that the models that I have prepared have all been for a single purpose and the underlying datamodel is effectively a single star schema. Additionally, it is very difficult to ‘read’ the dashboard/report/datamodel structure of a workspace when lots of things have been thrown at it.
Careful and tailored use of filters are very important and arguably move your reports from being relatively static to something that can be truly explored.
If you introduce fields into page filters it means that you can hyperlink to a filtered version of the page. This is the case even where the filter is empty. Some details for this are shown via the following link :-
Increase the canvas size
It’s a very simple way of increasing your screen real estate, once you get over the fact that users will need to scroll around your report. I would typically only increase the length, not the width.
This is one of the most powerful aspects of the product and certainly something that distinguishes it from a normal excel experience. It may feel strange at first, but it is worth persevering with.
Time intelligence comes ‘out of the box’ however you need to be aware that when you use it a hidden table is created every time that a date field is encountered and this can cause your file to be surprisingly large as a rule of thumb you’re probably adding about 1mb per date field. The biggest annoyance is that as a result that it takes a long time to upload it to the Power BI service. It’s never been a problem on my mini projects but it is relevant when moving to something more production based.
So that’s all from me for the moment please like it if you’ve found it useful. Do drop me a line or a comment and I’ll see what I can do to help out.
Update is out key takeaways are :-
Quick post on the DAX features that have helped me the most on my more recent projects:-
1 Switch(True(),Condition1, Result1, Condition2,Result2,OtherwiseX)
A very simple formula to avoid the need for nested if statements. Can be used for calculated columns – e.g. for Age Categories 0-20,21-30 etc. Can also be used in measures in conjuction with the HasOneValue(ColumnName) function.
2. Path function. Unbelievably effective at unpicking hierarchies contained within parent child relationships. PathItem and LookupValue are also important ways of further extracting meaning from this.
3. Summarize(Table) – Awesome way of taking a detail fact table and summarising it according to your needs. I found this most effective when calculating Bradford Factor which effectively looks at sickness records over 365 days adds them up and multiplies them by the distinct episodes of sickness squared. A bit technical, but I feel there’s a video coming on this.
Full structure goes along these lines:-
SUMMARIZE(<table>, <groupBy_columnName>[, <groupBy_columnName>]…[, <name>, <expression>]…)
Takes a little getting used to but there really is no equivalent within excel.
4. Union(table1, table2, table3) – Used to combine tables with identical fields.
5. Column Category URL – Essentially this allows you to push a url into a column and return an image back into a table visualisation. Sounds trivial but allowed us to generate a traffic light system relatively simply after having gone down many dead ends.
6. LookupValue – Very effective way of referencing a disconnected table and return the sort of results that you might expect from an excel vlookup.
7. Related() – yes I am guilty of overusing this, but it is tremendously effective at making it absolutely clear what data you are looking at in a complex model. Effectively pulls in data from a linked table.
8. Username() – the only DAX function that I am aware of that is capable of taking any characteristic of a user. In most cases this takes the form of your windows email@example.com.
When used in conjunction with the lookupvalue function you can create dynamic row level security. This link gives you the general pattern although it is aimed at SSAS tabular, which shares the DAX Tabular backbone https://powerbi.microsoft.com/en-us/documentation/powerbi-desktop-tutorial-row-level-security-onprem-ssas-tabular/
9. Calculate – probable the most important dax function. Changes the current filter context. Generally regarded as being one of the most important DAX functions.