SQL equivalent Power BI DAX functions – Part 2

Introduction

In this article, we are going to see window function in SQL and its equivalent Power BI DAX function.If you want to check out SQL basic, check it out in part 1.

    Specifications

    • Data – Loan transaction data
    • Environment – Postgres and Power BI

    What is window function?

    A window function performs an aggregate calculation across a set of table rows and returns single or multiple rows based on the function used with it.

    Window by ID partition

    Window function classification

    Window function comprises many different functions and it is grouped into 3 different categories.

    Value

    First_value

    The first_value() function is a window function that returns the first value in a group or within the group.

    Last_value

    The last_value() function is also a window function that returns the last value in a group or within the group.

    Both the functions are implemented in Power BI by using SUMX, FIRSTNONBLANK, LASTNONBLANK.

    Example;

    For each loan, the first and last transaction made is find by using these two functions. Its equivalent Power BI function also is given below.

    First value and last value in SQL
    First value and last value in Power BI

    Lead

    Lead is a window function that is used to fetch the next row value in the current row value itself.

    Lag

    Lag is also a window function that is used to retrieve the previous row value in the current row value itself.

    In Power BI, it is not easy to obtain lead and lag value for any record.

    But lag function can be obtained by the PREVIOUS function in Power BI where the date should be involved.click here to know more

    Even lead function can be obtained by creating the index and adding 1 to index to obtain the value. click here to know more

    Ranking

    Rank

    The Rank() function is a window function that assigns rank within the group or multiple groups. If two or more rows have the same rank it will skip the next rank.

    Dense rank

    The Dense_rank() function is also a window function similar to rank() function but it will not skip rank when two or more rows have the same ranks.  

    Both the rank functions can be implemented in Power BI DAX by using the RANKX() function with the parameter passed.

    Rank and Dense rank in SQL
    Rank and Dense rank in Power BI

    Row number

    Rownum function is used to create the row number in different ways like within a group or multiple groups.

    The row number generated by using the CALCULATE function in  Power BI.

    Example:

    In here, I explain the row number created within loan number ordering on the transaction-id

     

    Row number in SQL
    Row number in Power BI

    Percentile rank

    Percentile_rank() is a window function that returns the percentage of scores in its frequency distribution that is equal or lower than that.

     

    It is calculated in Power BI by using the CALCULATE, RANKX, and DIVIDE functions.

    Example:

     

    Percentile rank within loan id based on the transaction.

     

    Percentile rank in SQL
    Percentile rank in Power BI

    Cumulative sum

    Cumulative sum refers to running sum and it is implemented in SQL by using sum with a window function and in Power BI implemented by using CALCULATE and SUM function.

    Example:

     

    Running sum of transaction amount within the loan id.

     

    cumulative in SQL
    Cumulative in Power BI

    Aggregate

    Windows function acts on the aggregate functions and returns the output by row-level known as window aggregate function. The same can be obtained in Power BI by using the formatting option available at the field level.

    Example:

    Here I explain the aggregate function on the transaction records by the customer level.

    Some of the aggregate functions are:

    SUM  

    Return the sum of the transaction amount.

    AVG

    Return the average value of the transaction amount

    MIN

    Return the minimum of the transaction amount.

    MAX 

    Return the maximum of the transaction amount.

    COUNT

    Return count of records of the transaction.

    Windows aggregate function in SQL
    Aggregate function in Power BI

    Windows aggregate function will be faster than normal group by function in SQL. So these are the some of the window function equivalent in Power BI.

    2 thoughts on “SQL equivalent Power BI DAX functions – Part 2”

    1. Hello,

      thanks for these 2 articles! They’re really amazing for someone who is trying to do some DAX coding after years of SQL experience. Can you please clarify about ROW_NUMBER() equivalent one moment:
      – SQL let’s you define OVER(PARTITION BY …) – how to do this in DAX? All your examples show calculations only for one ID.

      Thanks,
      Danil

      1. 1. Row_number function I used selected data. If I remove the filter on one ID the row number order will be based on the transaction id ascending order.
        2. Cumulative function does the same OVER(PARTITION BY …). the only difference is the cumulative sum of each transaction partition by loan id.
        If you don’t want cumulative just remove the last expression txn_id

    Leave a Comment

    Your email address will not be published. Required fields are marked *