DAX Samples

sarahmriecke
Apr 19, 2023
5 min read

Updated: May 6, 2023

This blog post contains a repository of various DAX functions I've used these past few years to accomplish calculations at the request of clients and end users.

Please note: There is no sensitive or identifiable data in this code. Measures, columns, and tables may utilize placeholders.

Used Functions: VAR, CALCULATE, DATEADD, MIN, MAX, COUNTROWS, CALCULATETABLE, VALUES, EXCEPT, ISFILTERED, PARALLELPERIOD, USERELATIONSHIP, SELECTEDVALUE, FORMAT, DIVIDE, SWITCH, & CONCAT, && AND, || OR, IF, ALLEXCEPT, LOOKUPVALUE, EARLIER, COUNTROWS, AVERAGEX

Last Updated 5.6.2023

Calculate Past 3 Quarters

Based on the max date in the respective tables. This takes into account if data is not available yet for previous months.

var _today = today()
var _MaxDate = [OSMaxDate_GEN]
var _MinOSDate = [OSMinDate_GEN]
var _1QTR = IF(CALCULATE([MEASURE], DATEADD(SnapshotDate[Max_OS], -1, QUARTER)) >0, DATEADD(SnapshotDate[Max_OS], -1, QUARTER), _MinOSDate)
var _2QTR =  IF(CALCULATE([MEASURE], DATEADD(SnapshotDate[Max_OS], -2, QUARTER)) >0, DATEADD(SnapshotDate[Max_OS], -2, QUARTER), _MinOSDate)
var _3QTR =  IF(CALCULATE([MEASURE], DATEADD(SnapshotDate[Max_OS], -3, QUARTER)) >0, DATEADD(SnapshotDate[Max_OS], -3, QUARTER), _MinOSDate)

return IF(SnapshotDate[SnapshotDate] = _today, 1, IF(SnapshotDate[SnapshotDate] = _1QTR, 2, IF(SnapshotDate[SnapshotDate] = _2QTR, 3, IF(SnapshotDate[SnapshotDate] = _3QTR, 4, 0
))))

Values Found in Previous Month & Not Current Month

Creates a table based on the keys (Service IDs) from the previous month (based on the max month selected) and 1 table for the current month. devices found in the previous month but not in the current month were removed MoM. Those not in the previous month but in the current month were added MoM.

var _history = CALCULATETABLE(VALUES('Table'[Key]), Month('Date'[SnapshotDate]) = MONTH(MAX('Date'[SnapshotDate]))-1)
var _current = CALCULATETABLE(VALUES('Table'[Key]))
var devices_not_found = COUNTROWS( EXCEPT( _history, _current)) + 0
var new_devices = COUNTROWS( EXCEPT(_current, _history)) + 0

return  devices_not_found

Dynamic MoM % or QoQ % Change

Taking prior month or quater change based on whether month of quarter field are filtered on.

var PMo  = IF(ISBLANK(CALCULATE([Measure], PARALLELPERIOD('Date'[SnapshotDate], -1, MONTH))), [Measure], CALCULATE([Measure], PARALLELPERIOD('Date'[SnapshotDate], -1, MONTH)))
var PQu = IF(ISBLANK(CALCULATE([Measure], PARALLELPERIOD('Date'[SnapshotDate], -1, QUARTER))),[Measure], CALCULATE([Measure], PARALLELPERIOD('Date'[SnapshotDate], -1, QUARTER)))
var Selection = IF(ISFILTERED('Date'[SnapshotMonthInCalendar]), PMo, IF(ISFILTERED('Date'[SnapshotQuarterInCalendar]), PQu, blank()))

return
IFERROR(ROUNDDOWN(([Measure] - Selection)/ Selection, 2), [Measure] * 1)

First Tuesday of First Full Week on Month

Determines if a given Tuesday in a month is the first full week of the month.

IF(SnapshotDate[YYYYMM] = 202108, (1 + int(DIVIDE((SnapshotDate[SnapshotDate] - WEEKDAY(SnapshotDate[SnapshotDate], 3) + 1) - (DATE(year(SnapshotDate[SnapshotDate]), month(SnapshotDate[SnapshotDate]), 1)), 7))) - 1, 1 + int(DIVIDE((SnapshotDate[SnapshotDate] - WEEKDAY(SnapshotDate[SnapshotDate], 3) + 1) - (DATE(year(SnapshotDate[SnapshotDate]), month(SnapshotDate[SnapshotDate]), 1)), 7)))

Measure to Set KPI Value of 1/0/-1

Used in place of a KPI in a SSAS cube. Determines if the value is above, between, or below the target and thresholds.

IF([Measure] >= [UpperTarget Measure], 1, if([Measure] >= [LowerTarget Measure], 0, -1))

Varied Targets for Different Time Periods

There are multiple years in the dataset with different targets. The main connection between the table and date table was based on reported date. This will only pull the "current" values of year targets. In order to pull previous year's target an inactive relationship was created. The measure helps to pull the target based on that inactive relationship.

IF([PCT_PreviousPeriodQoQ_GEN] >= CALCULATE([UpperTargets_GEN], USERELATIONSHIP(SnapshotDate[-1QTR], 'BoardReporting_Year'[SnapshotYear])), 1, IF([PCT_PreviousPeriodQoQ_GEN] >= CALCULATE([LowerTargets_GEN], USERELATIONSHIP(SnapshotDate[-1QTR], 'BoardReporting_Year'[SnapshotYear])), 0, -1))

Applied Filters

Used to showcase applied filters. Best used with placing into a table (and removing the background). Will only show the items that have been filtered on.

var FirmName =  IF(ISFILTERED(Firm[Firm Name]), "Firm Name: " & SELECTEDVALUE(Firm[Firm Name], "Multiple Selected") & "; ", "")
var BusinessUnit =  IF(ISFILTERED('Business Unit'[Business Unit]), "Business Unit: " & SELECTEDVALUE('Business Unit'[Business Unit], "Multiple Selected") & "; ", "")
var Specialty =  IF(ISFILTERED(Firm[Specialty]), "Specialty: " & SELECTEDVALUE(Firm[Specialty], "Multiple Selected") & "; ", "")
var Trans =  IF(ISFILTERED(AR[RCM Trans Flag]), "Trans Flag: " & SELECTEDVALUE(AR[RCM Trans Flag], "Multiple Selected") & "; ", "")

return
FirmName  & BusinessUnit  & Specialty  & Trans   & Vendor  & PayerName  & CPTProduct  & CoverageType  & Responsible  & BalanceType  & BillType  & BilledStatus & CM & billing   & Primary & Aging

*Can define as many variables as needed to cover filters in hidden filter pane

Insight Text

Used to show a monthly change and % change in a text box as an added insight.

var _lastmonth = max('Posted Date'[Posted Month])
var _previousmonth = datevalue(month(_lastmonth - 30) & "/1/" & YEAR(_lastmonth - 30))
var _Last = CALCULATE([Adjustments],'Posted Date'[Posted Month] = _lastmonth)
var _previous = CALCULATE(_ProdSum_Measures[Adjustments],'Posted Date'[Posted Month] = _previousmonth)
var _difference = _Last - _previous
var _direction = IF(_difference < 0, "decreased", "increased")
var _directiondifference = format(ABS(_difference), "$#,0")
var _percentchange = format(round(divide(_difference, _previous, 0), 2), "#,0%")
return 
"Adjustments " & _direction & " " & _directiondifference & " (" & _percentchange & ") between " & FORMAT(_previousmonth, "MMMM") & " and " & FORMAT(_lastmonth, "MMMM") & "."

Date Related Text - Informative

Date Ranges

var _mindate = MIN(AR[Service Date])
var _maxdate = max(AR[Service Date])

return 
"Service Dates: " & _mindate & " - " & _maxdate

Last Refreshed/Updated Date

If pulling from one fact table, you do not need the min(max(), max()) just the max(date)

"Last Updated: " & DATEVALUE(min(MAX(AR[Date Reported]), max(ADR[date_reported])))

Dynamic ADR & DSO

This is "Dynamic" because the ADR could be rolled up from Business Unit to Firm.

ADR = 
var DSODays = max(ADR[RCMDays])
var calc = IF(DSODays >= 90, DIVIDE([DSO_Charges], 90), DIVIDE([DSO_Charges], DSODays))

return calc

DSO = DIVIDE([TotalAR], [ADR])

Dynamic Period Over Period Calculations

Dynamic based on min/max date range selected/filtered in the reporting.

TotalAR_PoP = 
var Beginning = min('AR Trending'[date_reported])
var Ending = max('AR Trending'[date_reported])
var FirstValue = CALCULATE([Total AR Trending], 'Reported Date'[Reported Date] = Beginning)
var CurrentValue = CALCULATE([Total AR Trending], 'Reported Date'[Reported Date] = Ending)

return CurrentValue - FirstValue

TotalAR_PoP% = 
var Beginning = min('AR Trending'[date_reported])
var Ending = max('AR Trending'[date_reported])
var FirstValue = CALCULATE([Total AR Trending], 'Reported Date'[Reported Date] = Beginning)
var CurrentValue = CALCULATE([Total AR Trending], 'Reported Date'[Reported Date] = Ending)

return (CurrentValue - FirstValue) / FirstValue

Defined Conditional Formatting

In conditional formatting of visuals, you are unable to change the color of something based on text, typically only numbers. One way to solve that is to use a measure with those value and hex code for the conditional formatting.

In the example below, the measure_text measure contains text of letter grades from A+ to F. This measure assigns a specific color from green to red for each letter grade.

Link to Excelerator BI with more information and details.

Measure  = 
SWITCH([Measure_Text], 
 "F", "#490a06",
 "D", "#92140c",
 "C", "#e18335",
 "B", "#f7c67e",
"A", "#9db4a3",
 "A+", "#5b8266",
blank())

Rolling Averages

This measure can be utilized to show a rolling average (I historically included such in a trending visual - line graph). The default of the shown measure below is a 30 day moving average, but can be adjusted.

 AverageX(
              SUMMARIZE(
DATESINPERIOD(SnapshotDate[SnapshotDate], LASTDATE(SnapshotDate[SnapshotDate]), -30, DAY)
              ,SnapshotDate[SnapshotDate]
              ,"Selected"
              ,SELECTEDMEASURE())
,SELECTEDMEASURE())

To adjust:

• SELECTEDMEASURE() gets replaced with your specific measure you want average.

• SnapthostDate[SnapshotDate] gets replaced with your specific date column

• -30, DAY gets replaced with your specific period you want for your rolling/moving average. (Day, month, quarter, and year are the options.)

Lookup Value Within Table

The below example is based on lookup up the prior week's KPI grade for a given Business Unit. Two criteria need to be met, it must be the grade for that specific Business Unit and from the previous week.

LOOKUPVALUE can also be used to lookup values across other tables, and acts very similarly to a VLOOKUP or INDEX MATCH in Excel. It must be able to return a single value.

This is a calculated column:

KPI_PW = LOOKUPVALUE('KPI'[KPI_Grade_Measure], 'KPI'[Business_Unit_ID], 'KPI'[Business_Unit_ID], 'KPI'[Reporting_Date], 'KPI'[Reporting_Date] - 7)

Calculate Consecutive Weeks

The below example was used to be able to count the consecutive weeks a business unit is escalated.

Utilized Power BI Forum to create calculated column.