Power BI DAX Functions: Basics to Real-World Cases

[uzuntasgokberk]

Introduction

Power BI is a suite of business analytics tools developed by Microsoft that allows you to analyze data and share insights. This post covers essential DAX functions such as CALCULATE, ALL, ALLSELECTED, ALLEXCEPT, DIVIDE, IF, and DATEADD, explaining them with basic measure functions. Following the basics, real-world cases will be discussed in detail, demonstrating how to effectively use these functions to showcase necessary insights.

CALCULATE Function:

Certain filtering or conditions are used to modify or customize calculations by considering them.

CALCULATE_SUM gives information about total SalesPiece from Export table

CALCULATE_SUM=
  CALCULATE(
    SUM('Export'[SalesPiece])
  )

CALCULATE SUM WITH DIMENSION FILTER gives information about filtering specify categorical values.

CALCULATE SUM WITH DIMENSION FILTER =
  CALCULATE (
    SUM( 'Export ' [SalesPiece]),
      FILTER(
      'Export',
      'Export' [Category] ="Elektronik" ||
      'Export' [Category] ="Gida"
      )
    )
  )

CALCULATE_SUM_WITH_NUMERIC_FILTER gives information about filtering specify numeric values.

Summarize you can use CALCULATE function to use MAX,SUM,AVG… for filtering specify dimension or numeric values.

Output:

ALL Function:

It overrides existing filters when analyzing a table or column, allowing you to evaluate the data in a broader context.

CALCULATE WITH ALL function emphasis removes all filters on category from export table and total SalesPiece from export table.

CALCULATE WITH ALL =
  CALCULATE(
    SUM( 'Export' [SalesPiece]),
    ALL ('Export' [Category])
  )

DIVIDE Function:

Performs division and returns alternate result or BLANK() on division by 0.

Sales% measure implement for spesific value divide by all values.

Sales% =
  DIVIDE (
    [CALCULATE_SUM],
    [CALCULATE_WITH_ALL]
    )

Output:

ALLSELECTED Function:

Removes context filters from columns and rows in the current query, while retaining all other context filters or explicit filters.

The ALLSELECTED function gets the context that represents all rows and columns in the query, while keeping explicit filters and contexts other than row and column filters. This function can be used to obtain visual totals in queries.

CALCULATE WITH ALLSELECTED emphasis that removes all filters on category from export table and total SalesPiece from export table. But if effects filters or explicit filters.

CALCULATE WITH ALLSELECTED =
  CALCULATE(
    SUM(
    'Export ' [SalesPiece]
    ),
    ALLSELECTED(
    "Export' [Category]
    )
   )

Output:

ALLEXCEPT Function:

Removes all context filters in the table except filters that have been applied to the specified columns.

CALCULATE_WITH_ALLEXCEPT gives information total SalesPieces and effects dimensions which is Category from Export table.

CALCULATE_WITH_ALLEXCEPT =
  CALCULATE(
    SUM(
    'Export ' [SalesPiece]
    ),
    ALLEXCEPT(
    'Export',
    'Export ' [Category]
    )
  )

Output:

IF function:

This function allows you to add a logical expression. It is used to evaluate conditional expressions and perform actions based on different results.

IF FUNCTION dax measure gives information about that we have turkish dimension values. Therefore, with ıf function ı can see the way as english values.

IF FUNCTION =
  IF(
  SELECTEDVALUE ('Export' [Category])="Gida",
  "Food",
    IF(
    SELECTEDVALUE ('Export '[Category])="Elektronik",
    "Electronic",
      IF(
      SELECTEDVALUE('Export' [Category])="ïthalat",
      "importation",
        IF(
        SELECTEDVALUE('Export '[Category])="Lojistik",
        "Logistic",
          "Total"
         )
        )
      )
    )
  )

Output:

DATEADD Function:

Returns a table that contains a column of dates, shifted either forward or backward in time by the specified number of intervals from the dates in the current context.

DATE_ADD dax measure gives last month Gerçekleşen / Actual numeric values.

DATE_ADD =
  CALCULATE (
    AVERAGE ('Storage' [Gerçeklesen / Actual]),
    DATEADD('Storage' [AyY11 / MonthYear], -1,MONTH)
  )

Output:

REAL CASE:

Color-coding by comparing the current month to the past month. The following steps should be considered for coloring conditions:

Color condition for the first month (January) of the current year: If the actual value is < the target value, it should be Red; if the actual value > the target value, it should be green.

For situations outside of January of the current year, the coloring condition is:

If the actual value >= the target value and the actual value > -1 actual value, print 1 (Green)

If the Actual Value = -1 equals the actual value OR the actual value < -1 actual value is less, AND the actual value >= the target value, print 2 (Yellow)

If the actual value < the target value, print 3 (Red)

And not using Power BI legends use general icons.

Firstly, creating Color_Measure measure to give for color conditional formatting.

Secondly, give the colors each numbers.

Thirdly,  Place an Icon as Text: An icon is placed using Windows + “.”

Output: