Showing posts with label Power BI Tutorials. Show all posts
Showing posts with label Power BI Tutorials. Show all posts

What is Measure or Calculated Column| Power BI DAX

What is Measure or Calculated Column| Power BI DAX

 

Types of DAX functions

1. Date and Time Functions

The date time functions carry out calculations on the date and time values. The data type of these values is always datetime data type.

 

CALENDAR

CALENDARAUTO

DATE

DATEDIFF

DATEVALUE

DAY

EOMONTH

HOUR

MINUTE

MONTH

NOW

SECOND

TIME

TIMEVALUE

TODAY

WEEKDAY

WEEKNUM

YEAR

YEARFRAC

 

2. Time Intelligence Functions

The time-intelligence functions are used to evaluate values over a fixed period such as days, weeks, months, quarter, years, etc. You can specify a time period using these functions and compare two scenarios in your report.

 

CLOSINGBALANCEMONTH

CLOSINGBALANCEQUARTER

CLOSINGBALANCEYEAR

DATEADD

DATESBETWEEN

DATESINPERIOD

DATESMTD

DATESQTD

DATESYTD

ENDOFMONTH

ENDOFQUARTER

ENDOFYEAR

FIRSTDATE

FIRSTNONBLANK

LASTDATE

NEXTQUARTER

LASTNONBLANK

NEXTDAY

NEXTMONTH

3. Information Functions

The information functions are used to provide certain information on the data values contained in rows and columns. It evaluates the given condition in a function for the value given and return TRUE or FALSE. For instance, the function ISERROR will return TRUE if the value evaluated contains an error.

 

CONTAINS

CUSTOMDATA

IN Operator / CONTAINSROW function

ISBLANK

ISERROR

ISEVEN

ISINSCOPE

ISLOGICAL

ISNONTEXT

ISNUMBER

ISODD

ISONORAFTER

ISTEXT

LOOKUPVALUE

USERNAME

4. Logical Functions

The logical functions are used to evaluate an expression or argument logically and return TRUE or FALSE if the condition is met or not.

 

AND

FALSE

IF

IFERROR

IN

NOT

OR

SWITCH

TRUE

5. Mathematical and Trigonometric Functions

The mathematical and trig functions are used to perform all sorts of mathematical functions on the referred values. Given below, is a list of all the available math and trig DAX functions in Power BI.

 

ABS

ACOS

ACOSH

ASIN

ASINH

ATAN

ATANH

CEILING

COMBIN

COMBINA

COS

COSH

CURRENCY

DEGREES

DIVIDE

EVEN

EXP

FACT

FLOOR

6. Statistical Functions

These functions carry out statistical and aggregation functions on data values in a DAX expression in Power BI. The list of available statistical functions is given below.

 

ADDCOLUMNS

APPROXIMATEDISTINCTCOUNT

AVERAGE

AVERAGEA

AVERAGEX

BETA.DIST

BETA.INV

CHISQ.INV

CHISQ.INV.RT

CONFIDENCE.NORM

CONFIDENCE.T

COUNT

COUNTA

COUNTAX

COUNTBLANK

COUNTROWS

COUNTX

CROSSJOIN

DISTINCTCOUNT

EXPON.DIST

GENERATE

GENERATEALL

GEOMEAN

GEOMEANX

MAX

MAXA

MAXX

MEDIAN

MEDIANX

MIN

MINA

MINX

7. Text Functions

The text functions in Power BI are very similar to the string functions of Excel. These functions evaluate string values.

 

 

BLANK

CODE

COMBINEVALUES

CONCATENATE

CONCATENATEX

EXACT

FIND

FIXED

FORMAT

LEFT

LEN

LOWER

MID

REPLACE

REPT

RIGHT

SEARCH

SUBSTITUTE

TRIM

UNICHAR

UPPER

VALUE

8. Parent-Child functions

The parent and child functions are used for data values that are a part of a parent-child hierarchy.

 

PATH

PATHCONTAINS

PATHITEM

PATHITEMREVERSE

PATHLENGTH

9. Other functions

There a bunch of functions that do not fit in any particular category. These are also very useful functions.

 

DATATABLE

ERROR

EXCEPT

GENERATESERIES

GROUPBY

INTERSECT

ISEMPTY

ISSELECTEDMEASURE

NATURALINNERJOIN

NATURALLEFTOUTERJOIN

SELECTEDSMEASURE

SELECTEDMEASUREFORMATSTRING

SELECTEDSMEASURENAME

SUMMARIZECOLUMNS

Table Constructor

TREATAS

UNION

VAR

10. Table functions

The table functions in DAX formulas for Power BI are used to apply operations and conditions on entire tables. The output of table functions is used as inputs in other expressions or arguments in a DAX formula. The results of these functions retain the relationships between columns of that table.

 

FILTER

ALL

VALUES

DISTINCT

RELATEDTABLE