Posted by
Andrew Gould
on 14 August 2021
This first video in our DAX for Power BI series is designed to get you set up and ready to follow along with the rest of the series, and to give you an idea of what you can do with DAX. You'll see quick examples of DAX calculated columns, DAX measures, DAX table expressions and using variables in DAX expressions.
Posted by
Andrew Gould
on 17 August 2021
This video explains how to create calculated columns in DAX. You'll learn about the important concept of Row Context and a variety of ways to reference a column in a DAX expression. You'll see how to add comments and lay out your code for readability. You'll learn about the order of operation and how to control it and how to use some basic DAX functions. You'll also see how DAX adjusts the data type of a column as you make changes to the expression. Finally, you'll learn how to concatenate multiple values to create a longer value.
Posted by
Andrew Gould
on 19 August 2021
This video shows you how to create calculated columns using the If function in DAX. You'll learn how to write basic logical tests, how to create columns to use as categories or values and how to write nested If functions. You'll also find out how to use the And function and && operator, as well as the Or function and || operator. Finally, you learn how to use the In operator in DAX.
Posted by
Andrew Gould
on 20 August 2021
This video explains how to use the Switch function in calculated columns in DAX. You'll learn how to test a column value against a list of constants, how to use the True function to enable your Switch function to include logical tests, and the importance of the order in which you write those tests.
Posted by
Andrew Gould
on 21 August 2021
This video shows you how to work with blanks in your DAX expressions for calculated columns. You'll learn you to test for blanks using the IsBlank function and the Blank function. You'll see the difference between the equal to and strictly equal to operators and the importance of that difference when it comes to working with blanks. You'll learn how to produce a blank as the result of an expression and what happens when you involve a blank in some basic arithmetic. Finally, you'll see how to concatenate a blank into a string and how to use the Coalesce function to produce a replacement value.
Posted by
Andrew Gould
on 23 August 2021
This video covers a variety of DAX functions for working with text. You'll learn how to join text together using the Concatenate and CombineValues functions and the concatenation operator. You'll see how to use the Format function to apply standard and custom formats to values. You'll learn how to extract text from the left, right and middle of a string, as well as how to locate the characters you want to extract using the Find or Search functions. You'll find out how to remove extra spaces from a string and how to calculate the length of a string. Finally, you'll see how to use the Replace or Substitute functions to replace one piece of text with another.
Posted by
Andrew Gould
on 24 August 2021
This video shows you the basics of working with dates in calculated columns. You'll learn what the Auto Date Time feature in Power BI does. You'll see how to use functions such as Year, Quarter, Month and Day to extract parts of dates and how to use the Format function to return month and day names. You'll learn how to write literal dates in your expressions and how to calculate a date from its constituent parts using the Date function. You'll see how to calculate future and past dates using DateAdd and why that dosn't always work, as well as how to get around the problem using basic arithmetic. FInally, you'll learn how to calculate the difference between dates using the DateDiff function.
Posted by
Andrew Gould
on 06 September 2021
This video explains how to deal with errors in your DAX calculated column expressions. You'll learn how to use the IsError and IfError functions to replace errors when they occur, as well as how to use the If function to avoid some errors in the first place. You'll see how to use the Divide function to help avoid divde by zero errors. You'll see errors related to data type conversions and also how to use the alternate result parameter of some functions to avoid errors.
Posted by
Andrew Gould
on 26 September 2021
This video explains some of the basic concepts of data models involving multiple tables in Power BI. You'll learn about one-to-many or many-to-one relationships and how to get Power BI to automatically detect relationships between tables. You'll see how to create relationships manually and learn some basic data model terminology. You'll see what happens when you have multiple tables which aren't related and how filters propagate through the relationships in your model. You'll also see how to manipulate the direction of a relationship to influence how filters behave. In the final part of the video you'll learn how and why to use the Related DAX function to reference fields in different tables.
Posted by
Andrew Gould
on 23 October 2021
This video explains how to use the LookupValue function in DAX. You'll learn how to lookup values between tables which don't have relationships and how to deal with items which return no matching value. You'll see how to nest LookupValue functions to search for values in a chain of tables. You'll also learn how to find values by matching on multiple columns and how to lookup values in the same table in a different column.
Posted by
Andrew Gould
on 29 October 2021
This video provides an introduction to working with DAX measures in Power BI. You'll learn how to add measures to a data model and how measures differ from calculated columns. You'll see how to organise the measures in your model using display folders and measure tables. You'll be introduced to the concept of filter context and how this affects the result returned by a measure. You'll see some examples of using measures to format visuals rather than to simply display a value. You'll also see a basic example of manipulating the filter context to influence which values are used by a measure.
Posted by
Andrew Gould
on 19 November 2021
This video explains how to use DAX aggregate functions such as Sum, Average, Min and Max to aggregate a single column. You'll also learn how to use SumX, AverageX, MinX and MaxX to aggregate the results of expressions.
Posted by
Andrew Gould
on 26 November 2021
This video shows you a variety of ways to count things in DAX. You'll learn how to count values in a column using the COUNT and COUNTA functions, and what effect blanks have on the result of the count. You'll see how to use the COUNTROWS function to avoid issues caused by blanks and how to count unique values using the DISTINCTCOUNT and DISTINCTCOUNTNOBLANK functions. Finally, you'll learn how to count the result of an expression using the COUNTX and COUNTAX functions, as well as how to combine these with the IF function to create a rudimentary CountIf setup.
Posted by
Andrew Gould
on 30 November 2021
This video explains how to use variables in DAX to break a large calculation into discrete parts. You'll learn how to declare and assign values to variables in calculated columns and measures, and how to refer to variables in your code. You'll see how to declare variables within a function to create variables with a limited scope, and how to create variables which can hold the results of a table expression.
Posted by
Andrew Gould
on 04 December 2021
This video shows you how to create and edit Quick Measures in Power BI Desktop to save time writing DAX code. You'll learn how to add quick measures, how to edit the code afterwards and how to create a range of examples.
Posted by
Andrew Gould
on 14 December 2021
This video shows you how to apply filters in your DAX measures using the Calculate, Filter and CalculateTable functions. You'll learn how to apply single and multiple filters, how to use the And, Or and In operators and how to work with numbers, text and dates. You'll also see the limitations of filter arguments in the Calculate and CalculateTable functions and how to nest the Filter function to get around these limitations.
Posted by
Andrew Gould
on 20 December 2021
This video explains how to remove filters in DAX measures using the All and RemoveFilters functions. You'll learn how to remove all the filters from a measure, how to remove filters from specific tables and how to remove filters from specific columns. You'll also see how to create measures which compare filtered and unfiltered values to show, for example, the percentage contribution of values to totals.
Posted by
Andrew Gould
on 21 December 2021
This video shows how to both replace existing filters in a measure and how to retain an existing filter using the KeepFilters function.
Posted by
Andrew Gould
on 24 December 2021
This video explains how to use the AllSelected and AllExcept functions to remove filters in your DAX measures. You'll learn how to remove all the filters and only the specified filters using the All function; how to remove all of the filters applied by a Power BI visual using the AllSelected function; and how to remove all filters except for the ones you specify using the AllExcept function.
Posted by
Andrew Gould
on 30 July 2022
This video explains how relationships in a data model affect filters in visuals and DAX measures. You'll learn how to use the CrossFilter function in DAX to control how filters propagate and return the correct results regardless of the cross filter direction setting in the relationship. You'll also see how to sync slicers without needing to enable bi-directional filters on every relationship in a model.
Posted by
Andrew Gould
on 15 January 2022
This video shows you how to get started with time intelligence functions in DAX. You'll learn how to make Power BI create a date table for each date column in your data model automatically. You'll also learn what the auto date table contains and why it's important for the operation of the time intelligence functions. You'll see how to display different levels of a date hierarchy and how to use the DateAdd function to modify the filter context applied to a measure to compare a calculation with past and future dates. Finally, you'll learn how to use the SamePeriodLastYear function as a shorthand for a specific use of the DateAdd function.
Posted by
Andrew Gould
on 15 January 2022
This video explains how to use DAX time intelligence functions to modify the filter context applied to a measure, allowing you to compare calculations for different time periods. You'll learn the differences between the DateAdd, SamePeriodLastYear, PreviousYear and ParallelPeriod functions. You'll also see how to enter specific dates using the DateValue and Date functions, and how to use these dates in the DatesBetween function. Finally, you'll learn how to calculate moving averages using FirstDate and LastDate functions and the DatesInPeriod function.
Posted by
Andrew Gould
on 17 January 2022
This video explains how to use a range of DAX Time Intelligence functions to calculate running totals and to-date values in your measures. You'll learn how to use the FirstDate and LastDate functions to calculate the start and end of intervals and combine these with the DatesBetween function. You'll see how to use the StartOfYear and EndOfYear functions, as well as the DatesYTD, DatesQTD and TotalYTD functions. Finally, you'll learn how to set the year end date to a different date than December 31st for a range of time intelligence functions.
Posted by
Andrew Gould
on 19 January 2022
This video shows you how to use time intelligence functions in DAX to return values from the start and end of a period. You'll learn how to use the FirstDate and LastDate functions as filter arguments for the Calculate function. You'll also see a range of functions used to return the start and end dates of yearly, quarterly and monthly intervals. The video also explains the ClosingBalance and OpeningBalance functions and explains how OpeningBalance is different to using the StartOf interval functions. You'll also learn how to work with incomplete date ranges and how to use the FirstNonBlank and LastNonBlank functions to return values even when your data model doesn't contain values for the start or end of standard intervals.
Posted by
Andrew Gould
on 16 February 2022
Learn how to replace the auto date-time calendars in Power BI with a custom calendar using the CalendarAuto and Calendar DAX functions.
Posted by
Andrew Gould
on 15 April 2022
This video shows you how to create a calendar table using DAX which includes a financial year, quarter and month column. You'll see how to calculate thes values using the AddColumns and Calendar tables, as well as how to use the calendar in measures with some of DAX's time intelligence functions.
Posted by
Andrew Gould
on 16 April 2022
In this video you'll see a couple of techniques for creating calendars to handle multiple date fields in the same data model. You'll learn how to use the Calendar function to create multiple calendars spanning sensible date ranges which is useful when your different date fields hold very different date ranges. You'll also learn how to use the CalendarAuto function which is useful when you want a single calendar to handle all the date fields in the model. If you're using a single calendar you'll also need to know how to create and manipulate multiple relationships to the same table, and how to use the UseRelationship function in measures to ensure that you're calculating the correct values!
Posted by
Andrew Gould
on 14 October 2023
This video shows you how to calculate rolling or moving average calculations in DAX. You'll learn how to use the DATESINPERIOD function to expand the date range over which an average is calculated, specifying the number of years, quarters or months. You'll also see how to work out the average of other aggregations such as a yearly, quarterly or monthly sum of a value. You'll see how to handle blank values using the COALESCE function. Finally, you'll learn how to use a numeric range parameter to allow the end user to control the period over which the rolling average is calculated.
Posted by
Andrew Gould
on 07 August 2022
This video shows you how to reference the values of a column in a measure expression. You'll learn how to use the Values or Distinct functions to return a list of unique values for a column. You'll see how to use HasOneValue or HasOneFilter to check if only one unique value has been returned. You'll also learn how to use the SelectedValue function to replace a combinatiion of the If, HasOneValue and Values functions. The video also explains how to use these functions for several practical examples in Power BI, including creating dynamic visual titles with sync slicers and drill through filters, as well as how to create a basic disconnected slicer.
Posted by
Andrew Gould
on 25 March 2023
In this video you'll learn how to use the ConcatenateX function to join values from a column into a single string. You'll learn how to create delimiters including new line characters, and how to sort the items in the concatenated string. You'll see how to insert conditional statements and emojis into the string and how to filter the items using functions like FILTER and TOPN. You'll also see how to create dynamic visual titles which is useful when combined with the Sync Slicers feature. Finally, you'll see how to use the ISFILTERED and ISCROSSFILTERED functions to help control the length of the concatenated list.
Posted by
Andrew Gould
on 30 September 2023
This video explains how to use slicers to pick values without filtering visuals in a report. You'll learn how to add a disconnected table to the data model and how to populate a slicer with fields from it. You'll use the SELECTEDVALUE and VALUES functions to return the items picked in the disconnected slicer and then use the returned values in measures to compare against other values. You'll learn how to set up the slicer to allow for multiple selections or single selections only. You'll also see a few examples of using measures to apply conditional formatting to visuals and create dynamic titles.
Posted by
Andrew Gould
on 01 October 2023
In this video you'll learn how to use the value selected in a disconnected slicer to apply conditional formatting to visuals in a report. You'll learn how to test if columns have been filtered or cross-filtered, how to return single or multiple selected values and how to return colours using names or hex codes.
Posted by
Andrew Gould
on 22 October 2023
This video shows you how to use field parameters to allow end users to control which fields are displayed in visualisations in your Power BI reports. You'll learn how to create and edit field parameter tables and allow users to pick fields from slicers to control the display of visuals.
Posted by
Andrew Gould
on 29 October 2023
This video introduces the concept of ranking values in DAX using the RANKX function. You'll see a quick overview of the RANKX, RANK.EQ and RANK functions before using the RANKX function to create a calculated column. You'll learn how to control the sort order of the ranking, as well as how to deal with tied results using either a Skip or Dense rank. You'll also see how to use the RANKX function to rank aggregated values in a measure, including how to use the ALL function to ensure the ranking is evaluated correctly. You'll learn how to hide ranked values on total rows in a visual using the HASONEVALUE or ISINSCOPE functions, and how to use ALLSELECTED to rank only the values currently displayed in the visual. You'll also see how to return only the top 3 results based on the rank. The final part of the video explains how to calculate ranks for different levels of a group hierarchy in a matrix visual, including how to calculate subtotals correctly, and how to return a rank for each level of grouping.
Posted by
Andrew Gould
on 04 November 2023
This video explains how to use the RANK function to rank values in DAX measures. You'll learn about some of the differences between RANK and RANKX. You'll see how to control tied results with the SKIP and DENSE settings. You'll learn how to use the ORDERBY function to rank a category on multiple values. You'll see how to treat BLANK values in a ranking, including how to hide blanks completely. You'll use the ISINSCOPE function to decide which measure to return in a visual with multiple category fields. Finally, you'll learn how to solve a problem involving sort by columns in your data model.
Posted by
Andrew Gould
on 18 December 2023
This video shows you how to use the optional, third parameter of the RANKX function to rank a value in a list of other values. You'll learn how to rank a measure calculated for one set of filtered values against a list of values calculated with a different filter which enables you to, for example, rank values for one year against the same list of values in a previous year.
Posted by
Andrew Gould
on 19 December 2023
This video explains the concept of context transition in DAX. You'll learn about the differences between row context and filter context and how to invoke context transition to create a filter context from a row context. You'll see several functions whose arguments are evaluated in a row context and understand the importance of invoking context transition to make your measures return the correct results. You'll also see how to perform multiple context transitions in more complex measures.
