Power BI Function to Concatenate Date and Text Fields

This guide will help you create a Power BI DAX function that converts a date field to a text field, concatenating it with another text field. The specific fields we're working with are Rule16_EYR_CheckYieldRateValues[ValuationDate] (a date) and Rule16_EYR_CheckYieldRateValues[CntTagUniqueName] (a text field).

Requirements

• Power BI Desktop
• Basic understanding of DAX syntax

Function Overview

Purpose

The purpose of this DAX function is to convert a date to text format and concatenate it with another field, ensuring that the output is a single text string.

Parameters

• Rule16_EYR_CheckYieldRateValues[ValuationDate]: The date field that needs conversion.
• Rule16_EYR_CheckYieldRateValues[CntTagUniqueName]: The text field to concatenate with.

Return Type

This function will return a string value.

DAX Code Implementation

The following DAX code defines a calculated column to achieve the desired functionality.

ConcatenatedDateUniqueName = 
    // Check if ValuationDate and CntTagUniqueName are not blank
    VAR ValuationDate = Rule16_EYR_CheckYieldRateValues[ValuationDate]
    VAR CntTag = Rule16_EYR_CheckYieldRateValues[CntTagUniqueName]
    
    RETURN
    IF(
        NOT(ISBLANK(ValuationDate)) && NOT(ISBLANK(CntTag)),
        // Convert date to text
        FORMAT(ValuationDate, "YYYY-MM-DD") & " " & CntTag,
        // Return a default message if any field is blank
        "Date or Tag is blank"
    )

Explanation of the Code

1. Variable Declaration:

   • ValuationDate retrieves the date from the specified column.
   • CntTag retrieves the text from the CntTagUniqueName column.

2. Return Statement:

   • The IF function checks if both fields are not blank (ISBLANK).
   • If both fields are filled, it concatenates the date (formatted in "YYYY-MM-DD") with the text.
   • If either field is blank, it returns a message indicating the issue.

3. Formatting:

   • FORMAT is used to convert the date to a string in a specified output format.

Usage Example

Assuming you have a table named Rule16_EYR_CheckYieldRateValues in your Power BI model, follow these steps:

1. Open the Data view in Power BI.
2. Navigate to the Rule16_EYR_CheckYieldRateValues table.
3. Create a new calculated column by pasting the DAX code.
4. Name the new column ConcatenatedDateUniqueName.

Result

This will create a new column that contains strings formatted as "YYYY-MM-DD CntTagUniqueName", allowing for better readability and reporting capabilities in your Power BI reports.

Conclusion

This approach provides a simple yet effective way to combine date and text fields into one concatenated output in Power BI. For further learning on DAX and advanced Power BI functionalities, consider exploring resources on the Enterprise DNA Platform.