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 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.
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 firstname.lastname@example.org.
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.
This is at least a part of my story which has taken me from being completely ignorant about birds through to having a slightly better understanding through, of course, Power BI.
Image View Custom Visual
And this is a little walkthrough of how to use it plus a little bit of dax.
Various Vids that I’ve found helpful in this area:-
Embed App in