_edited_edited.png)
DAX is an expression of formula used in Power BI. It is a language function used by the users to interact with the data model. It helps to get more information on the existing data as it contains collection of functions, constants and operators that can be used for the formula to calculate and get more values that are needed. DAX contains numeric data types such as integers, decimals, currency, date-time and Boolean. Non-numerical data types used in DAX are Binary objects and strings. The variant data types can also be used to return different data types depending on the conditions, The column in regular table cannot have such data type but a DAX measure or general DAX expression can have variant data type.
Example: IF ([measure] > 0, 1, ‘’n/a’’)
With DAX, the quality of data models can be improved. Even with the basic calculations, informative visualizations can be created with the new measures. There are two primary calculations ‘Calculate Columns’ and ‘Calculated Measures’ that can be created using DAX. I have used patient’s demographic and patient history dataset to clarify the data model. Some of the generally used functions and its examples in Power BI are as the following:
1. Aggregate (MIN, MAX, Average, SUM, SUMX)
AVERAGE: Returns the value to the average of the column. The column should contain numerical values.
Syntax: AVERAGE(<column>)
Example: Average Height = Average (Height(m))
Minimum: Returns the value to the minimum number in the column. It should be numerical values.
Syntax: MIN (<column>)
Example: Minimum Height = MIN(Height(m))
Maximum: Returns the value to the maximum number in the column. It should be numerical.
Syntax: MAX (<column>)
Example: Minimum Height = MIN(Height(m))
SUM: Returns the value to the total numbers in the column. The values should be numerical.
Syntax: SUM (<column>)
Example: Total Height = SUM (Height(m))
SUMX: Defines an expression to sum over in order to filter the values that are being summed. The column should contain numerical values to perform this expression.
Syntax: SUMX (<table>, <expression>)
Example: Age Difference = SUMX (Demographic, Demographic [before age] – Demographic [after age])
2. Count (DISTINCTCOUNT, COUNT, COUNTA, COUNTROWS, COUNTBLANK)
DISTINCTCOUNT: This shows the total number or counts of unique values in the column. As we can see below, the column “Group” has two unique values.
Syntax: COUNT(<column>)
Example: Count of Groups = DINTINCTCOUNT(Demographics [Group])
COUNT: Total number of values in the column.
Syntax: COUNT(<column>)
Example: Total no. of group = COUNT (Demographics [Group)]
COUNTA: It counts cells containing any kind of values, either it has error or the text is empty.
Syntax: COUNT(<column>)
Example: Total no. of group = COUNTA (Demographics [Group)]
COUNTROWS: It is used to count unique values in the column as per the filter context.
Syntax: CALCULATE(COUNTROWS (<table>, <expression>)
Example: No. of Latino = CALCULATE(COUNTROWS (Demographics), Demographics [Race] = “Latino”)
COUNTBLANK: It counts the blank values of the column.
Syntax: COUNTBLANK(<column>)
Example: Blank values = COUNTA (Demographics [Group)]
3. Logical (AND, OR, NOT, IF, INERROR)
AND: This operator is used for more than one column. It checks if all the arguments are TRUE and returns TRUE.
Syntax: AND (<logical1>,<logical2>)
Example: White Patient with diabetes = IF(AND(Demographic[Group]=”DM”, Demographics[Race] = “White”, “White patient with DM”, “NA”)
OR: This operator is used for more than one column. It checks if all the arguments are TRUE and returns TRUE otherwise FALSE.
Syntax: OR (<logical1>,<logical2>)
Example: White Patient with diabetes = IF(OR(Demographic[Group]=”DM”, Demographics[Race] = “White”, “White patient with DM”, “NA”)
NOT: It returns values as TRUE or FALSE as per the given condition.
Syntax: NOT(<logical>)
Example: Age not 35+ = NOT(Demographics[Age]<35,”true”, “false”)
IFERROR: It is based on IF function and uses same error messages, but has fewer arguments.
IFERROR(value, value_if_error)
Example: Age error = IFERROR(Demographics[Age],0)
4. Text (REPLACE, SEARCH, UPPER, LOWER, FIXED, CONCATENATE)
REPLACE: It returns the value to text string.
Syntax: REPLACE(<old_text>, <start_num>, <num_chars>, <new_text>)
Example: Replace = REPLACE (Demographics[Column],1,0, Demographics[Age])
SEARCH: It searches the text that you want to find.
Syntax: SEARCH(<find_text>, <within_text>[, [<start_num>][, <NotFoundValue>]])
Example: Search Race = IF (SEARCH(“Asian”, Demographics[Race], 1, 0, TRUE, FALSE)
UPPER: It returns the values in the cells to the uppercase letters
Syntax: UPPER (text)
Example: Uppercase = UPPER(Demographics[Column])
FIXED: It rounds the numbers in the columns.
Syntax: FIXED(<number>, <decimals>, <no_commas>)
Example: Fixed BMI = FIXED (Demographics[BMI],0,FALSE)
CONCATENATE: It joins two string from different columns to a single string.
Syntax: CONCATENATE(<text1>, <text2>)
Example: Concatenated_column = CONCATENATE (‘Patient History’[Tobacco Use],” “ &‘Patient History’[Alcohol Use],”
5. Date (DATE, HOUR, WEEKDAY, NOW, EOMONTH)
DATE: This function generates the corresponding date by taking the integers that are input as arguments.
Syntax: DATE(<year>, <month>, <day>)
Example: date = DATE(2009,7,8)
HOUR: It returns the integer number from 0-23.
Syntax: HOUR (<datetime>)
Example: hour = HOUR(‘Diagnosis[DiagnosedTime]) or hour = HOUR (“January 1, 2024 2:00 PM”)
WEEKDAY: It returns the integer value from 1-7.
Syntax: WEEKDAY (<date>,<return_type>)
Example: WEEKDAY ([AdmittedDate]+1)
NOW: It is used to display or calculate current date and time.
Syntax: NOW()
Example: Current = NOW()+4.5, it returns date and time plus 4..5 days
EOMONTH: It returns the value in datetime format.
Syntax: EOMONTH (<start_date>,<months>)
Example: EOMONTH(“January 1, 2024”, 1.5)
6. Information (ISBLANK, ISNUMBER, ISTEXT, ISNONTEXT, ISERROR)
ISBLANK: It returns the value as TRUE if the cells are blank and FALSE if it has value.
Syntax: ISBLANK (<value>)
Example: Black Medical History = ISBLANK(‘Patient History’[DM Patient Medical History])
ISNUMBER: The value is returned as TRUE if numeric otherwise it shows FALSE.
Syntax: ISNUMBER(<value>)
Example: Check If numbers = ISNUMBER(‘Patient History’[DM Patient Medical History])
ISTEXT: It returns value as TRUE if text, otherwise FALSE.
Syntax: ISTEXT(<value>)
Example: Check If text = ISTEXT (‘Patient History’[DM Patient Medical History])
ISNONTEXT: It appears Boolean as TRUE if the cells value is not text or blank and FALSE it the value is in text.
Syntax: ISNONTEXT(<value>)
Example: Check isnontext = ISNONTEXT (‘Patient History’[DM Patient Medical History])
ISERRROR: It the respective column has error values, it shows TRUE otherwise, FALSE.
Syntax: ISERRROR(<value>)
Example: Check ISERRROR = ISERRROR (‘Patient History’[DM Patient Medical History])
With the use of these DAX functions, the data model could be simplified and informative. Along with these functions, there are many other functions that can be used to define the date and have deeper analysis and clear visualizations. Moreover, in order to locate the referenced columns and return accurate outcome, most of the DAX functions require that the relationship exists among those tables.
Happy Learning !!