Dynamic DAX Label Retrieval with Month Context

DAX Code Analysis

Objective: The given DAX code snippet retrieves local language-specific labels for a dashboard and appends the current month to this label, encapsulated within parentheses.

Breakdown of the Code

LOOKUPVALUE( 
   DashboardLabelTranslation[LabelText], 
     DashboardLabelTranslation[LabelName],"TypeOfPest" 
     ,DashboardLabelTranslation[LanguageID],FIRSTNONBLANK(LanguageDim[LanguageID],"EN")
) &  " (" & MAX(DateDim[MonthName]) &")"

Section 1: LOOKUPVALUE Function

Function Purpose: LOOKUPVALUE is used to retrieve a single value from a table when one or more search conditions are provided.

Parameters of LOOKUPVALUE:

1. Result Column (DashboardLabelTranslation[LabelText]): The column from which the value is retrieved.
2. Search Column 1 (DashboardLabelTranslation[LabelName]): The column to search for the first condition.
3. Search Value 1 ("TypeOfPest"): The value to look for in Search Column 1.
4. Search Column 2 (DashboardLabelTranslation[LanguageID]): The column to search for the second condition.
5. Search Value 2 (FIRSTNONBLANK(LanguageDim[LanguageID],"EN")): The value to look for in Search Column 2, derived using the FIRSTNONBLANK function.

This effectively retrieves the translation (label text) for "TypeOfPest" in the first non-blank language ID.

Section 2: FIRSTNONBLANK Function

Function Purpose: FIRSTNONBLANK(column, expression) returns the first non-blank value within column where the specified expression is true.

In this context:

• LanguageDim[LanguageID] is the column being evaluated.
• "EN" is returned if there are no non-blank values.

This ensures that the translation is specific to the first non-blank language ID, typically the user's current or default language setting.

Section 3: MAX Function

Function Purpose: MAX(column) returns the maximum value within a column.

In this context:

• DateDim[MonthName] retrieves the name of the month with the most recent date in the current filter context.

This ensures that the current month's name is appended to the label.

Section 4: String Concatenation

Concatenation Symbols:

• & is used in DAX for string concatenation.

The concatenation process in the code:

• Appends " (" to the retrieved label text.
• Appends the current month's name using MAX(DateDim[MonthName]).
• Appends ")" to close the parenthesis.

Complete Code Explanation

The code combines the operations in a single LOOKUPVALUE function to:

1. Retrieve the localized label text for "TypeOfPest".
2. Ensure the language ID is correctly identified using FIRSTNONBLANK.
3. Append the current month name within parentheses to the label text.

Full DAX Snippet

LOOKUPVALUE( 
   DashboardLabelTranslation[LabelText], 
   DashboardLabelTranslation[LabelName], "TypeOfPest", 
   DashboardLabelTranslation[LanguageID], FIRSTNONBLANK(LanguageDim[LanguageID], "EN")
) & " (" & MAX(DateDim[MonthName]) & ")"

Summary

• LOOKUPVALUE: Retrieves the translation text for "TypeOfPest".
• FIRSTNONBLANK: Ensures the label is in the appropriate language.
• MAX: Fetches the latest month's name.
• String Concatenation: Combines these elements into a formatted string.

This code is useful for dynamically generating translated labels with contextual information (such as the current month) for dashboards. For further learning, exploring DAX functions on the Enterprise DNA platform is recommended.