Lets’ Explore 4 Key Areas Where DAX Can Be Used!!
DAX MEASURES
CALCULATED COLUMNS
CALCULATED TABLES
ROW-LEVEL SECURITY
1. Introduction To DAX Measures
Definition: Measures Are Dynamic Calculations That Are Computed On The Fly Based On User Interactions With Reports.
Indeed, we could say that Measures' results can be considered as dynamic nature, since their results can be changed depending on the FILTER CONTEXT!!!.,
Thus, we can conclude that those Measures are always used in Reporting, such as a PowerBI Report, where the results of those calculations are evaluated.
For example, consider this below "Total Sales" Measure.
Total Sales = SUM ( Sales [Revenue] )
In this case, this measure will result in the Total SUM of "Revenue" column from Sales Table. However, when we put together this measure (Total Sales) with some other filters (such as "Sales Region", "Product Types" etc. ) in a certain visual, that measure has to re-evaluate the results, depending on that current filter context!
However, anyway, Measures are mainly used for aggregations, calculations, and creating KPIs (Key Performance Indicators).
2. Calculated Columns
Definition: Calculated Columns Are Computed During The Data Refresh Process And Stored In The Data Model.
One of the superb features of Power BI Data Modelling is that it allows the imported dataset, to add new columns in the data model tables whenever necessary.
Thus, the usage of Calculated Columns is very useful when we need to add certain columns, in the Power BI Data Tables, that will be used as Filters in your Power BI Report visuals.
For example, lets' say we have two existing columns: "Revenue" and "Cost" in our Sales Table, that is already imported into the Power BI Solution File.
If, we want to simply get a new "Profit" column, we could easily done in that Power BI, by using the DAX formula in the "Calculated Column" creation.
Profit = Sales[Revenue] - Sales[Cost]
In fact, Calculated columns are useful when you need new data points that can be used in slicers, filters, or as part of other calculations.
3. Calculated Tables
Definition: Calculated Tables Are Often Created By Solely Using DAX Functions. This results In A New, More Advanced Way To Extract Key Values In A Separate Table, Or Either To Be Stated As The Dimension Table. Not Only That, The Entire Date Tables Can Be Created Using Those Calculated Table DAX Functions.
In fact, one of the superb qualities of DAX in Power BI is such that Calculated Tables are defined by the DAX formulas where the results are derived from all or part of other tables in the same Data Model.
Thus, it is very useful in creating standalone tables, that is available for queries, as fully operable as any other table.
Not only that, Calculated tables are extremely useful when extracting the filtered rowsets, or a subset or superset of columns from other existing tables.
In fact, Calculated tables are re-calculated if any source data tables are refreshed or updated!
For example, a date table can be easily calculated in time intelligence calculations as follows:
DateTable = CALENDAR(DATE(2023, 1, 1), DATE(2023, 12, 31))
Therefore, we can conclude that Calculated tables are used for generating new tables based on expressions and existing data, useful for creating lookup tables or aggregating data.
4. Row-Level Security (RLS)
Definition: Row-level Security (RLS) In Power Bi Is A Feature That Allows You To Control Access To Data At The Row Level Within Your Reports. It Enables You To Restrict Data Visibility For Certain Users, Ensuring That They Can Only See The Data That’s Relevant Or Permitted For Them To View.
Basically, when developing the RLS, the users must develop a DAX formula, that must evaluate to Boolean TRUE / FALSE expression.
For example, to allow only viewing the data of "USA Customers", we can define the RLS as follows:
Customers[Country] = "USA"
Moreover, authors of the PowerBI Reports, can perform the 3 main tasks in RLS as follows:
(1) Creating Roles: Authors can define specific roles and specify DAX filters that determine what data each role can access.
(2) Assign Users to Roles: Authors can assign which types of users could access to specific roles in order to access the report data
(3) Dynamic Data Masking: this is for Dynamic RLS where the DAX formulas automatically mask data based on the user's role. Certain RLS DAX functions such as USERNAME ( ) or USERPRINCIPALNAME ( ) are used to dynamically filter data views based on the logged-in user's information.
