Data Analysis Expressions (DAX) is a query and modelling language for SQL Server Analysis Services (SSAS) Tabular, Power-BI, and Excel Power Pivot. Calculated columns, tables, and user-defined Measures can be created at a very sophisticated level.
How useful DAX is in business practice is impressively illustrated by a development in the area of the Development in Pay Act. This relatively new law provides employees with the right to demand valid proof from their employer for the commensurability of their own remuneration in comparison to employees with similar activities. With the help of the HR-Analytics solution of noventum, which also relies on the DAX formula language, companies can comply with this obligation.
WHAT IS DAX?
With the help of Data Analysis Expressions (DAX), very powerful, yet intuitively usable Measures can be created in SSAS Tabular models, e.g. allowing to nest aggregations with respect to different dimensions, store complex calculation formulas, use time-intelligence functionalities, and modify and/or set filter contexts. Despite the complex definitions made possible, end users and/or analysts can work with these Measures intuitively due to the good design, without running risk of producing materially wrong results.
One of the implementations for the implementation of the Transparency in Pay Act in the noventum solution HR-Analytics is a beautiful example for the power, elegance, simplicity, and intuitive use of DAX-based Measures within an SSAS Tabular model. Put simply, the Transparency in Pay Act stipulates that companies must provide their employees – under certain circumstances – with information on the average wage. [For material details regarding the Transparency in Pay Act, see the white paper by noventum consultant Dr. Benjamin Klör .] From a technical point of view, this wage information is, strictly speaking, not an “average”, but rather a “median” of the salaries in a certain comparison group consisting of employees with similar characteristics. But the following example can be implemented just as well with average, minimum, and maximum.
EXAMPLE: TRANSPARENCY IN PAY ACT WITH HR-ANALYTICS
Based on an already existing data model, only three additional DAX Measures had to be defined for the analyses with respect to the Transparency in Pay Act.
The first Measure extrapolates the gross salary components from the corresponding table of facts fact_Salaries with the help of the corresponding degree of employment from the dim_CapacityLevel to a “Full Time Equivalent” (FTE). With this, for example, USD 2,000 of an employee with a degree of employment of 50 % are converted to USD 4,000 (per FTE):
...
Read the complete article on novum online, the newsdesk of noventum consulting
How useful DAX is in business practice is impressively illustrated by a development in the area of the Development in Pay Act. This relatively new law provides employees with the right to demand valid proof from their employer for the commensurability of their own remuneration in comparison to employees with similar activities. With the help of the HR-Analytics solution of noventum, which also relies on the DAX formula language, companies can comply with this obligation.
WHAT IS DAX?
With the help of Data Analysis Expressions (DAX), very powerful, yet intuitively usable Measures can be created in SSAS Tabular models, e.g. allowing to nest aggregations with respect to different dimensions, store complex calculation formulas, use time-intelligence functionalities, and modify and/or set filter contexts. Despite the complex definitions made possible, end users and/or analysts can work with these Measures intuitively due to the good design, without running risk of producing materially wrong results.
One of the implementations for the implementation of the Transparency in Pay Act in the noventum solution HR-Analytics is a beautiful example for the power, elegance, simplicity, and intuitive use of DAX-based Measures within an SSAS Tabular model. Put simply, the Transparency in Pay Act stipulates that companies must provide their employees – under certain circumstances – with information on the average wage. [For material details regarding the Transparency in Pay Act, see the white paper by noventum consultant Dr. Benjamin Klör .] From a technical point of view, this wage information is, strictly speaking, not an “average”, but rather a “median” of the salaries in a certain comparison group consisting of employees with similar characteristics. But the following example can be implemented just as well with average, minimum, and maximum.
EXAMPLE: TRANSPARENCY IN PAY ACT WITH HR-ANALYTICS
Based on an already existing data model, only three additional DAX Measures had to be defined for the analyses with respect to the Transparency in Pay Act.
The first Measure extrapolates the gross salary components from the corresponding table of facts fact_Salaries with the help of the corresponding degree of employment from the dim_CapacityLevel to a “Full Time Equivalent” (FTE). With this, for example, USD 2,000 of an employee with a degree of employment of 50 % are converted to USD 4,000 (per FTE):
...
Read the complete article on novum online, the newsdesk of noventum consulting
