Powerbi development 14th: Dax expressions (Time + filter + relationship)

The Dax expression contains time intelligence-related functions for date dimension accumulation, year-on-year comparison, and period-over-period comparison. Powerbi can create relationships and use filters to influence the computing context.

1. Time relationship

Dax expressions can be used to calculate the sum in two ways. totalxtd () is the syntactic sugar of datesxtd (), making it easier for powerbi to calculate the sum of sums.

All time relational functions contain a special dates parameter, which has three forms:

• Reference to the date/time column in the format of datetable [date_column]
• Table expression, returns a single list of date/time types
• Boolean expression, used to define a single list of date/time values.

To apply the time relationship and analyze data by time, it is best to create a date dimension table separately and create a 1: n association with the fact table to ensure that the relationship is active. The granularity of the date dimension is set to day to ensure that the date dimension table includes all the date data.

1. Calculate the sum directly

In Dax, three functions are directly used to calculate the sum of sums. totalmtd is calculated based on the current month, totalqtd is calculated based on the current quarter, and totalytd is calculated based on the current year:

TOTALMTD(<expression>,<dates>[,<filter>]) TOTALQTD(<expression>,<dates>[,<filter>])  TOTALYTD(<expression>,<dates>[,<filter>][,<year_end_date>])  

The expression parameter is an aggregate Scalar Value expression. dates is a field containing a date, filter is a filter, and return a Boolean value.

For example, calculate the current sales volume:

= TOTALMTD(SUM(InternetSales[SalesAmount]),DateTime[DateKey])  

2. returns all dates of xtd.

Return to all the current dates. The dates parameter is a table that contains only one date column. The function uses the first date from dates as the benchmark:

DATESMTD(<dates>)  DATESQTD(<dates>)  DATESYTD(<dates> [,<year_end_date>]) 

The datesmtd () function is applicable to the date dimension. The date dimension must have continuous non-repeated dates. From January 1, January 1, December 31 last year, the first year of the specified data, the function returns a single list, the table consists of the date between the first month of the current date in the context and the current date in the context.

=CALCULATE(SUM(InternetSales[SalesAmount]), DATESMTD(DateTime[DateKey]))  

3. Calculate the year-on-year ratio (same period of the previous year)

The parallelperiod function is used to calculate parallel periods. A parallel date refers to moving forward or backward multiple time intervals (intervals) on the dates parameter. This function returns a table containing a parallel date, this function can be used to calculate the year-on-year ratio:

PARALLELPERIOD(<dates>,<number_of_intervals>,<interval>)

Parameter notes:

• Dates specifies the current date
• Interval specifies the interval. Valid values are year, quarter, and month.
• Number_of_intervals specifies the time interval for moving forward or backward

This function obtains the current date set in the column specified by dates, moves the specified interval between the first date and the last date, and returns all consecutive dates between the two shift dates. If the interval is a partial range of months, quarters, or years, any part of the month in the result will also be filled in to complete the entire interval.

For example, to roll back for 12 months, move the specified interval between the minimum date and the maximum date in datetime [datekey], and then return all consecutive dates between the two shift dates, calculate the sales volume (sales_amount) corresponding to these dates ).

CALCULATE([Sales_Amount]*1.1,PARALLELPERIOD(DateTime[DateKey],-12,MONTH))

In this example, the second parameter of calculate is a table.

Another function is sameperiodlastyear (), which is the package of parallelperiod (datetime [datekey],-12, month:

SAMEPERIODLASTYEAR(<dates>)

4. Calculate the period-over-period (previous day, month, quarter, or year)

The previous + (day/month/quarter/year) function is a function that moves the specified date forward. The parameter is a data table that contains the date and returns a data table that contains the date.

PREVIOUSDAY(<dates>)  PREVIOUSMONTH(<dates>) PREVIOUSQUARTER(<dates>)PREVIOUSYEAR(<dates>[,<year_end_date>])  

For the previusmonth () function, this function uses the first date in dates (input parameter) as the benchmark and returns all dates of the last month of the date. For example, if the first date in the dates parameter is January 1, June 10, 2009, this function returns all dates of January 1, May 2009.

=CALCULATE(SUM(InternetSales[SalesAmount]), PREVIOUSMONTH(Date[DateKey]))  

2. Filtering

Filter related functions. These functions are related to the filter chart (slicer) on powerbi ).

1. The value selected by the filter (unique value)

The currently selected value of the filter can be obtained through the function:

SELECTEDVALUE(<columnName>[, <alternateResult>]) 

Parameter notes:

• Columnname: A column name that has been saved. It cannot be an expression. This function returns this value when the context of columnname is only filtered into a different value;
• Alternateresult: Optional. The default value is blank (). If the context of columnname is filtered to 0 or multiple unique values, alternateresult is returned;

When the filter is selected only one value, the function returns the selected value.

2. The value selected by the filter (multi-value)

The values () function returns a single column table specified by the columnname parameter. The table contains all unique values of the column.

VALUES(<ColumnName>)  

This function is affected by the filter. When the values function is used in the filtered context, the unique value returned by values is affected by the filter. For example, if you filter by region and return a list of cities, the values () function only includes the cities in the regions allowed by the filter.

=COUNTROWS(VALUES(InternetSales[SalesOrderNumber]))  

The values function is similar to the distinct function. The only difference is that the values function returns unknown because the associated table contains unmatched data rows, it is similar to the null value in the right table of left join.

Combined with the concatenatex function, the unique values of all filters can be connected to strings. Note that the Dax expression uses & to connect strings.

3. Relationship

You can create multiple relationships between tables. However, only one link is active and the relationship is the default one. By default, the measurement expression uses the default link application filter for interactive computing.

USERELATIONSHIP(<columnName1>,<columnName2>) 

Userelationship uses the existing relationship in the model to identify the relationship through its endpoint column. This function is used to specify the relationship to be used in a specific calculation. In userelationship, the relationship status is not important, that is, whether the relationship is active does not affect the use of this function. Even if the link is inactive, it is used to overwrite any other active relationships that may exist in the model but are not mentioned in function parameters.

The userelationship function does not return any values. It only enables the specified relationship during calculation and is only used in the function that uses filter as a parameter. For example, calculate, calculatetable, closingbalancemonth, closingbalancequarter, closingbalanceyear, totalmtd, totalqtd, and totalytd.

 

Reference:

Dax reference

Dax time intelligence functions

Powerbi development 14th: Dax expressions (Time + filter + relationship)