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.