In this series, I have introduced Calculated Tables, Calculated Columns, and It’s time to introduce Measures. Before moving on to introduce Row Level Security next week.
Data Analysis Expressions (DAX), also used in other tools asides Power BI as I mentioned in the Overview write up.
But as far as Power BI goes, the earlier mentioned four are the main uses of the language.
And I feel (and many will agree) that the Expressions in DAX is more about measures than any other.
I mean, a lot can be done with Power BI without having to write a single Measure, but with Measures, you can only imagine how much more analysis can be done.
To express yourself in English or any language, you need a good hang of the language. The same goes for DAX, especially measures.
It is easier to get away with Calculated Tables and Calculated Columns, not so easy with Measures.
That’s why over 95% of write ups (including in this series) and videos is always about Measures.
A measure is a dynamic and portable store of formula.
Unlike Calculated Tables and Calculated Columns that produce results physically seen as Tables and Columns respectively, and largely static, the result of a Measure can only be seen when visualized.
And the results of the same measure will be different based on what it is being visualized by.
1.To create a Measure in Power BI, either click on the Modeling Tab and select New Measure, or, right click on a Table form the Fields Pane and select New Measure.
2.Then give the measure a sensible name and write your formula and hit the enter key on your keyboard.
The Measure immediately appears on the Fields Pane, under the last Selected Table, or the Table that was right clicked to create the Measure.
It shows a calculator icon beside it to signify that it’s a Measure.
Taking you back history lane, Measures at one time used to be called Calculated Fields.
1. Same Measure can be visualized differently.
For example, a measure written to sum Sales column can be used to display any category within the data table or a related table.
2. A Measure does not belong to any particular data table
Although, a Measure has to be physically located on a table within the data model, it is not a part of any table, and the results are not impacted by the table where it is located.
For example, we can change the physical location of a Measure from one table to another and the results remain the same.
3. A Measure can be referenced by another Measure.
A measure can serve as input for another measure.
This is one of the most beauty features of Measures because it can help break down formulas into different pieces which makes it easier to write complicate formulas and audit.
The next point clearly shows one of these benefits.
4. Changing the name or computation of a base measure automatically updates everywhere it is referenced.
I mean, if you change the name of a code referenced by another code, it should naturally return some kind of error, saying the name can not be found or something.
Not on Measures. Everything continues to work like nothing happened.
There are not many things needed to master DAX. Just 2 things. Functions and Evaluation Context.
However, the details in those two contains some technicalities that may seem taunting without a good foundation, and enough practice time.
Two things that this series aims to provide to you.
Before you learn to speak or write in English or any other language, you had to know a couple of English vocabularies.
But knowing a couple of vocabs doesn’t make you speak or write the language correctly without proper Grammar.
In DAX, Functions are like the Vocabulary while Evaluation Contexts are about the Grammar.
After the Row Level Security post next week, we will return home to Measures.
We will start to discuss the technicalities involved in mastering Measures, which of course, translate to mastering DAX.
We have a lot to cover. Vocabulary and Grammar to learn. You can download the data set here for your practice.