DAX in Power BI | Learn Power BI DAX Functions Step by Step


DAX In Power BI – Table of Content

What is DAX?

DAX stands for Data Analysis Expression. It is used for data analysis and calculations. DAX is a collection of functions, operators and constants. All these are evaluated as one formula to get the result. These formulas are very useful in Business Intelligence tools like Power BI. In DAX, complete code is written inside a function. So, it is called a functional language.

A DAX expression that is executable may contain nested functions, conditional statements, value references, etc. DAX formulas have two primary data types: numeric and others. The numeric data types include currency, decimals, etc., whereas others include string and binary objects. In a DAX formula, we can use values of the mixed data types as inputs. The conversion will take place during the time of formula execution automatically. As per your instructions, the output values will be converted into data types. The data scientists can use the data sets in DAX to the fullest. They can discover new ways to calculate data values with the help of DAX. In DAX, expressions are evaluated from the innermost function going to the outer function one by one. 

Become a Power BI Certified professional by learning this HKR Power BI Training !

What is Power BI?

Power BI is a Business Intelligence and Data Visualization tool used to convert data from data sources into interactive dashboards and analysis reports. For interactive visualizations, BI offers a cloud-based service. It provides a simple interface for the end-user. With the help of this, the end user can create their reports and dashboards. For different platforms, different versions of Power BI like Desktop, mobile Power BI, and Service-based apps are used. For Business Intelligence, it provides multiple services and software connectors.

Use of DAX in PowerBI: 

In Power BI, we can use DAX for calculated columns, Measures, and Tables. Computed columns allow us to create new columns based on the data given. 

For example, if you want to add the “Final Price” column in the table, then the DAX function is used to calculate the new column only if the quantity and price is available.

EX:

Price = List_Items[Quantity]*List _Items[MRP]

Here each row will have its calculated value.

We can also perform calculations using measures without adding any data. This is helpful for reports. Here the price can be displayed without the need for adding a new column to store it. 

EX:

Total MRP column*Total Quantity Column.

DAX functions used on tables return the entire tables. For example, to generate a list of the countries in which the organization has its clients use the function –

Cities touched = DISTINCT (Customers[City])

Basic knowledge of Power BI Desktop for a user is enough to create reports with all the available data. But if you want to use advanced calculations in the Power BI reports, you need DAX. 95% of Power BI potentials as an analytical tool is missed if you don’t use DAX. For example, if you want to make a visual to analyze growth percentages across different states of a country, the data fields that you import are not enough for that purpose. For this, new measures using DAX Language are to be made. DAX with Power makes the data analysis an innovative and intelligent approach.

Click here to learn Power BI Tutorial

Power BI Training

  • Master Your Craft
  • Lifetime LMS & Faculty Access
  • 24/7 online expert support
  • Real-world & Project Based Learning

Some of the Useful functions of DAX in PowerBI:

DAX functions are predefined formulas that perform the calculation on values provided to it in arguments. Every function performs a particular operation on the enclosed values in an argument. In a DAX formula, you can use one or more arguments. Some of the other functions of DAX are:

Date and Time functions:

Calculations on date and time values are performed by date and time functions. The data type of these functions is the datetime data type always. Some of the Date and Time functions are CALENDARAUTO, CALENDAR, DATE, DATEVALUE, DATEDIFF, DAY, HOUR, MINUTE, EOMONTH, MONTH, SECOND, NOW, TIMEVALUE, TIME, TODAY, WEEKDAY, WEEKNUM, YEARFRAC, YEAR.

Time Intelligence Functions:

These functions are used to evaluate values over a fixed period, such as years, quarter, months, weeks, days, etc. You can compare two scenarios in your report by specifying the time using these functions. Some of the Time Intelligence Functions are CLOSINGBALANCEYEAR, CLOSINGBALANCEMONTH, CLOSINGBALANCEYEAR, DATESINPERIOD, DATESBETWEEN, DATEADD, DATESQTD, DATESYTD, DATESMTD, ENDOFYEAR, ENDOFMONTH, ENDOFQUARTER, FIRSTNONBLANK, FIRSTDATE, LASTDATE, LASTDATE, NEXTQUARTER, NEXTMONTH, NEXTDAY.

Information Functions: 

Information Functions provide information related to the data values in the rows and columns. For the given values, it evaluates the given condition in the functions and returns True or False. Some of the Information Functions are CUSTOMDATA, CONTAINS, CONTAINSROW, ISERROR, ISBLANK, ISINSCOPE, ISEVEN, ISODD, ISNUMBER, ISLOGICAL, ISNONTEXT, ISTEXT, ISONORAFTER, USERNAME, LOOKUPVALUE.

Logical Functions: 

These functions are used to logically evaluate an expression or argument and return true or false based on the condition. Some of the Logical Functions are TRUE, FALSE, AND, OR, IF, IFERROR, IN, NOT, SWITCH.

Mathematical and Trigonometric Functions: 

These are the functions that are used to perform all sorts of mathematical functions on the values referred. Some of the math and trigonometric DAX functions available in PowerBI are ACOS, ACOSH, ABS, ASINH, ASIN, ATANH, ATAN, COMBINA, COMBIN, COS, COSH, DEGREES, CURRENCY, EVEN, EXP, DIVIDE, FACT, FLOOR.

Business Intelligence & Analytics, power-bi-dax-description-0, Business Intelligence & Analytics, power-bi-dax-description-1

Subscribe to our YouTube channel to get new updates..!

Statistical Functions: 

The functions used to carry out aggregation and statistical functions on data values in a DAX expression in Power BI. Some of the Statistical DAX functions available in PowerBI are APPROXIMATEDISTINCTCOUNT, ADDCOLUMNS, AVERAGEX, AVERAGEA, AVERAGE, COUNT, COUNTA, COUNTX, MAX, MAXA, MAXX, MEDIAN, MEDIANX, MIN, MINA, MINX, GEOMEAN, GEOMEANX, GENERATE, GENERATEALL, CROSSJOIN, DISTINCTCOUNT, etc. 

Text Functions:

These functions are similar to the string functions of Excel. These functions are used to evaluate string values. Some of the text DAX functions available in Power BI are: CODE, BLANK, COMBINEVALUES, CONCATENATEX, CONCATENATE, EXACT, FIND, FORMAT, FIXED, LEN, LEFT, LOWER, MID, REPLACE, RIGHT, REPT, SUBSTITUTE, SEARCH, TRIM, UNICHAR, VALUE, UPPER. 

Parent-child functions:

These are the functions used for the data values that are part of a parent-child hierarchy. Some of the Parent-child functions DAX functions available in Power BI are PATH, PATHLENGTH, PATHITEM, PATHCONTAINS, PATHITEMREVERSE.

Table functions: 

These functions are used to apply operations and conditions on entire tables. The output generated from table functions is used as the input in other arguments or expressions in a DAX formula. The results of these functions retain relationships between the table columns. Some of the table functions in Power BI are ALL, VALUES, FILTER, DISTINCT, RELATEDTABLE.

There are some functions that are very useful but do not fall under any category: ERROR, EXCEPT, GENERATESERIES, DATATABLE, GROUPBY, INTERSECT, ISEMPTY,ISSELECTEDMEASURE,NATURALINNERJOIN,NATURALLEFTOUTERJOIN,SELECTEDMEASURE,TREATAS,UNION,VAR,SELECTEDMEASUREFORMATSTRING, SELECTEDMEASURENAME, SUMMARIZECOLUMNS.

Click here to get latest Power BI Interview Questions and Answers for 2022

Some of the important DAX functions are

Average: This function is used to find the average from a given set of values. 
Ex:

Avgcomm - AVERAGE(List_Items[Price])

Max: This function is used to find the maximum from a set of values.
Ex:

Highsale = MAX(List_Items[Price])

Min: This function is used to find the minimum from a set of values.
Ex:

Lowestsale = MIN(List_Items[Price])

Count: This function is used to count any numerical values.
Ex:

TicketVolume - COUNT(Invoices[Ticket])

Concatenate: This function is used to join values in calculated columns. You can use ConcatenateX if you are using measures.
Ex:

ProMrp = CONCATENATE(List_Items[Items], List_ Items[MRP]

TotalYTD: This function is used to calculate the sum from the beginning of the current year to a specific date. Calculations are performed based on the calendar date, not according to the financial year.
Ex:

Cumisales = TOTALYTD(SUM(List_Items[Price]) , Invoices[Date])

All: This function returns everything. It ignores filters.

Power BI Training

Weekday / Weekend Batches

Conclusion 

In this blog, we have learnt about DAX, Power BI, and the benefits of DAX in Power BI. DAX functions can perform all the advanced calculations in Power BI. Using DAX functions in Power BI allows us to use most of the Power BI potentials. I hope you found this blog helpful. If you have any queries related to DAX in Power BI, you can comment below.

Related Articles:



Source link

Leave a Reply

Subscribe to Our Newsletter

Get our latest articles delivered straight to your inbox. No spam, we promise.

Recent Reviews


What is Peoplesoft – Table of Content

What is Peoplesoft?

PeopleSoft is a database application with the objective of providing business solutions in the areas of human resources, finance, and many more. It is a web application that may be used from any PC with access to the Internet. It can be configured so that it works with an organization’s business processes. 

Peoplesoft is an oracle product and is a collection of applications that medium and large companies integrate as workforce management solutions. Originally, PeopleSoft application was developed to support human resources and finance, over the years, however Peoplesoft has expanded with a great number of applications and tools for corporate business activities. Some uses that corporations and businesses are having for integrating PeopleSoft comprise many aspects of management such as communications, materials, and payroll management.

         We have the perfect professional PeopleSoft Admin Training course for you. Enroll now!

 

The following are popular applications that PeopleSoft includes:

  • PeopleSoft Selective Adoption 9.2 allows you to select the updates you would like to apply and when you want. It will enable you to keep key strategic customizations in the settings and measures you evaluate. It also delivers updates approximately every ten weeks.
  • PeopleSoft Application Engine allows us to develop, test, and run programs online or in batches that can handle a high background volume. These programs are similar to the COBOL applications, however the PeopleSoft application engine offers more effective updates because the program database includes all the necessary data to update it.
  • PeopleSoft in Oracle Cloud provides access to PeopleSoft products from Oracle Cloud. Oracle Compute Cloud and Marketplace enables companies to access and deploy a variety of PeopleSoft applications, like PeopleSoft Human Capital Management and commercial applications.
  • With the PeopleSoft deployment framework, we can transfer data into virtual systems in the Oracle cloud. In addition, Data can also be migrated from PeopleSoft Development and Testing into Oracle Cloud.
  • PeopleTools 8.55 is the same as PeopleSoft Deployment Framework as we may transfer customized data to Oracle Cloud and build several cluster nodes in the Oracle Cloud and PeopleSoft database.

All the above are some applications for companies to use in the PeopleSoft suite. According to the management requirements of the business, users can try a selected line of products to track, manage, and plan the operations of the business.

PeopleSoft Admin Training

  • Master Your Craft
  • Lifetime LMS & Faculty Access
  • 24/7 online expert support
  • Real-world & Project Based Learning

What is the purpose of the PeopleSoft software?

Basically, PeopleSoft applications are the tools used by large companies to effectively manage customer relationships, enterprise performance, human resources, financial and supply chains using Customer relationship management system, enterprise performance management system, human resources management system, and financial and supply chain management system.
Human resources management system: Along with payroll management, the HR department can monitor the hiring process from the posting of job openings and the selection of candidates to arranging interviews and the orientation of new employees. Peoplesoft’s Human resources management system also enables the Human resources department to access and manage employee information and timesheets and update key information such as taxes and benefits.

Customer relationship management system: It allows the companies to organize and manage each step of marketing processes using Peoplesoft customer relationship management. All the data related to marketing metrics, sales analytics, customer conversation, satisfaction, and purchases can be managed using the PeopleSoft application.

Financial and Supply Chains Management: It allows users to make inquiries of requesters, review the results of the evaluation of listed vendors, enter project balances, and alter project team tasks such as integration and completion dates. Additional uses of PeopleSoft covers import templates, update time reports, delete outdated spreadsheets, and the capability to organize and carry out mass transactions.

Enterprise performance management: It is helpful in identifying reports and specific execution control parameters for report processing, selecting the frequency of execution of a process, defining and changing the dates of the project and process, and streamlining the management of operations between the departments within an enterprise.

These core uses of the PeopleSoft software suite are the most common to businesses and companies. With respect to the human resources management system, PeopleSoft is proving to be a very effective solution to streamline, track and monitor typical processes of this type of department. As a result, Human Resources rely on PeopleSoft applications to organize employee information, tasks, payroll, departments and benefits, and employee benefits.

Want to Become a Master in PeopleSoft Financial? Then visit HKR to Learn PeopleSoft Financial Training!

others, what-is-peoplesoft-description-0, others, what-is-peoplesoft-description-1

Subscribe to our YouTube channel to get new updates..!

What benefits does PeopleSoft provide?

Peoplesoft gives a single system of record input and reporting abilities for all the financial transactions in all UMBC units.

  • The end-user completes transactions and enters the system only once and makes it accessible for multiple purposes like human resources, financial, reporting, etc.
  • Monitoring the status of transactions is available online, which almost eliminates the need for multiple phone calls about the status of transactions.
  • A number of transactions are processed online like purchase orders, pcard, requisition, position management, etc.
  • Approval of Transactions has been made simpler.
  • Some of the transactions may be approved and can be submitted online, resulting in internal controls and reducing the need to move documents from one office to another.
  • Signatures needed for standard business processes have been reduced while remaining consistent with state policies.
  • Capacity to produce general reports in real-time.
  • Business practices can be accessible from anywhere, on-campus or off-campus, at all times, as long as you have a computer along with a PeopleSoft Security connection and internal access.

How can PeopleSoft help you build a career?

How can PeopleSoft help in career advancement and skill development?
PeopleSoft offers a wealth of career advancement and skill development opportunities. By becoming proficient in PeopleSoft applications, individuals can advance their careers in the technology field. They can also continuously develop their skills as PeopleSoft is constantly updated with new technology, allowing users to stay ahead of the curve and up to date with the latest technological developments.

How can PeopleSoft help in gaining cross-functional knowledge and networking opportunities?
PeopleSoft applications in HR, CRM, FSCM, and EPM provide individuals with cross-functional knowledge. By working with these modules, individuals can gain a comprehensive understanding of various business processes and develop cross-functional expertise. Additionally, PeopleSoft users have the opportunity to connect and network with professionals both inside and outside their organization, which can lead to personal and professional growth.

How can PeopleSoft help in improving communication and teamwork abilities?
PeopleSoft requires collaboration and communication with multiple departments and stakeholders. This collaborative environment fosters the improvement of communication and teamwork abilities. By working with different teams and departments within the organization, individuals can enhance their communication skills and develop effective teamwork abilities.

How can PeopleSoft help in developing critical thinking and problem-solving skills?
PeopleSoft requires critical thinking and problem-solving skills, which can be further developed and utilized in other areas. By using PeopleSoft, individuals can enhance their critical thinking abilities by streamlining processes, managing projects, and effectively analyzing data. These skills can be transferred and applied in various aspects of their career.

How can PeopleSoft help in increasing job security?
Using PeopleSoft can increase job security because businesses will always need individuals who can effectively manage HR, CRM, FSCM, and EPM processes. As PeopleSoft skills are in high demand, individuals with these skills are more likely to have job security and stability in their careers.

How can PeopleSoft help in opening up new job opportunities with other organizations?
PeopleSoft skills are in high demand in the job market, which can open up new job opportunities with other organizations. By showcasing your proficiency in PeopleSoft applications, you can demonstrate your ability to effectively manage various business processes and increase your chances of securing new job opportunities.

How can PeopleSoft help in advancing within your current organization?
PeopleSoft can help in advancing within your current organization by providing the opportunity to acquire a valuable and sought-after skill set. By becoming proficient in PeopleSoft applications, you can effectively manage HR, CRM, FSCM, and EPM processes, which can contribute to career advancement within the organization.

                  Click here to get frequently asked PeopleSoft Admin interview questions for freshers & experienced professionals

PeopleSoft Admin Training

Weekday / Weekend Batches

What is the future outlook for PeopleSoft?

The future outlook for PeopleSoft appears highly promising, projecting it to remain a leading enterprise software solution for an extensive period of time. Its continued success is attributed to several key factors. One such factor lies in its substantial and dependable user base, which has consistently relied on PeopleSoft’s capabilities and expansive range of features. Additionally, PeopleSoft has established a commendable track record of delivering powerful and efficient solutions to its clientele. These factors make PeopleSoft an increasingly preferred option for companies, regardless of their size or industry. As a result, it is anticipated that PeopleSoft will continue to thrive and maintain its prominence in the enterprise software market for the foreseeable future.

What is PeopleSoft CRM and what features does it provide?

PeopleSoft CRM is a powerful customer relationship management (CRM) solution developed by Oracle. Designed for enterprise-level organizations, it encompasses a wide range of tools and features that facilitate a deeper understanding of customers and drive enhancements in sales, marketing, and service performance. By leveraging PeopleSoft CRM, businesses can build robust customer relationships, foster customer loyalty, and seize more business opportunities.

One of the key features of PeopleSoft CRM is its analytics capabilities, enabling organizations to gain valuable insights into customer behavior, preferences, and trends. These insights can help businesses make informed decisions and develop effective strategies to enhance customer satisfaction and drive revenue growth.

In addition to analytics, PeopleSoft CRM also provides collaboration features that promote efficient communication and teamwork within the organization. This fosters collaboration among different departments or teams involved in managing customer relationships, leading to more targeted and personalized customer interactions.

Process automation is another noteworthy feature of PeopleSoft CRM, which streamlines and automates various customer-related processes. This ensures that businesses can efficiently manage and track customer interactions, sales cycles, and service requests, resulting in improved productivity and operational efficiency.

Moreover, PeopleSoft CRM offers seamless mobile integration, allowing users to access and interact with customer data on-the-go. By enabling mobile access, businesses can ensure that sales representatives and service agents are equipped with real-time customer information, empowering them to provide prompt and personalized service.

Overall, PeopleSoft CRM is a comprehensive solution that goes beyond basic CRM functionalities, offering powerful analytics, collaboration tools, process automation, and mobile integration features. It equips organizations with the capabilities needed to develop stronger customer relationships, increase customer loyalty, and capitalize on valuable business opportunities.

What is CRM’s complete form?

CRM’s complete form is an abbreviation for Customer Relationship Management.

What is the difference between SAP and PeopleSoft?

SAP and PeopleSoft are both software solutions aimed at enhancing operational efficiency within organizations, but they differ in terms of their primary focuses and functionalities. SAP places a strong emphasis on analytics and business intelligence, providing tools that enable companies to gather and analyze data to gain valuable insights. On the other hand, PeopleSoft’s software is designed to enhance specific departments within an organization, such as HR, CRP (Customer Relationship Management), and finance. It aims to streamline and improve processes within these departments, offering solutions tailored to their unique needs. Ultimately, while both SAP and PeopleSoft offer software solutions to streamline operations, SAP focuses on analytics and business intelligence, while PeopleSoft specializes in enhancing HR, CRP, and finance departments.

Is PeopleSoft a database?

No, PeopleSoft is not a database. While it does involve data management, PeopleSoft is primarily an enterprise resource planning (ERP) software designed to enhance operational efficiency, automate important business processes, and enhance customer satisfaction. While it does utilize databases to store and retrieve information, it goes beyond traditional database functionality to provide comprehensive solutions for businesses.

What is PeopleSoft HCM and what features does it offer?

PeopleSoft is an Oracle product that offers a wide range of applications and tools for medium and large companies seeking comprehensive workforce management solutions. Originally developed to support human resources and finance, PeopleSoft has expanded over the years to include numerous applications and tools to support various corporate business activities.

One of the notable applications within the PeopleSoft suite is PeopleSoft HCM (Human Capital Management). PeopleSoft HCM is designed to streamline and optimize the entire employee experience, from recruitment to retirement. This comprehensive ERP system enables organizations to efficiently store and manage employee data, measure performance, and facilitate seamless communication between departments and employees.

With PeopleSoft HCM, organizations can seamlessly navigate the onboarding process, ensuring a smooth transition for new hires. It also provides robust features to track and manage training and development initiatives, allowing businesses to invest in the growth and success of their workforce. Furthermore, PeopleSoft HCM simplifies payroll management, ensuring accurate and timely compensation for employees. Additionally, it offers performance review functionalities, enabling organizations to assess and enhance employee performance.

In addition to PeopleSoft HCM, the PeopleSoft suite encompasses a variety of other applications and tools that cater to different management requirements. For instance, PeopleSoft Selective Adoption 9.2 allows users to selectively apply updates and customize settings based on their specific needs. The PeopleSoft Application Engine empowers businesses to develop, test, and run high-volume programs efficiently. Furthermore, PeopleSoft in Oracle Cloud provides seamless access to PeopleSoft products, enabling companies to deploy and utilize PeopleSoft Human Capital Management and other commercial applications.

The PeopleSoft suite also incorporates the PeopleSoft Deployment Framework, which facilitates data migration to Oracle Cloud and allows for the creation of multiple cluster nodes and databases. These features enable businesses to leverage the power of the cloud while retaining their customized data and configurations.

In conclusion, PeopleSoft is a comprehensive suite of applications and tools, with PeopleSoft HCM serving as a vital component specifically designed for managing the entire employee lifecycle. Its features and functionalities, including employee data management, performance tracking, communication facilitation, onboarding support, training and development monitoring, payroll management, and performance reviews, empower organizations to maximize the potential and success of their workforce.

Conclusion:

In this blog, we have learned about Peoplesoft, the purpose of Peoplesoft software, the benefits of PeopleSoft, and how Peoplesoft can help us to build our careers. We hope you found all this information helpful. If you want to know any other details of Peoplesoft, feel free to comment below.

Another Related Articles:



Source link