Forum Discussion

MrRong's avatar
MrRong
Frequent Visitor
2 years ago
Solved

Selectedmeasureformatstring DAX Calculation Item

  Hi everyone,   I ran into an error while trying to create a Calculation Group.   I have a Measure:   As you can see, the data type depends on the CalcType.   I created a Test cal...
DAX Calculation
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi MrRong ,
    To troubleshoot this problem, this function can be used in the dynamic format of the MEASURE. This DAX function returns the format string set for the currently evaluated metric. You can then use conditional logic to set the format string dynamically based on SELECTEDMEASUREFORMATSTRING.To solve this problem, this function can be used in the dynamic format of the MEASURE. This DAX function returns the format string set for the currently evaluated metric. You can then use conditional logic to set the format string dynamically based on SELECTEDMEASUREFORMATSTRING.

     

    VAR CurrentFormatString = SELECTEDMEASUREFORMATSTRING()
VAR CalcType = SELECTEDVALUE ( PL_header[CalcType] )

RETURN
SWITCH (
    TRUE(),
     CalcType = 1, [PL_additive_total_Actual],
     CalcType = 2, [PL_running_total_Actual],
     CalcType = 3, [PL_%_of_Revenue_Actual],
     CalcType = 4, [PL_%_of_running_Total_Actual]
    CurrentFormatString 
)

     

    For more information on how to use , please refer to the official documentation
    SELECTEDMEASUREFORMATSTRING function (DAX) - DAX | Microsoft Learn
    For more information on calculating group formats, you can refer to these documents
    Dynamic format strings with calculation groups - SQLBI
    Controlling Format Strings in Calculation Groups - SQLBI

    Best regards

    Albert He

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly


     

  • OwenAuger's avatar
    OwenAuger
    2 years ago

    Hi again MrRong

     

    I see Anonymous has also replied but I'll reply directly to your post. 

     

    1. Measure looks good 🙂

    2. The measure's dynamic format string looks good 🙂

    3. The calculation items don't look right.

    • The calculation item expressions for Actual, Actual PY and Variance PY should remain unchanged from your original version, i.e. including SELECTEDMEASURE() within the expressions.
    • However, you should enable Dynamic format string for each calculation item and set it to SELECTEDMEASUREFORMATSTRING().
    • Generally speaking, it would only make sense to use SELECTEDMEASUREFORMATSTRING() within the Format string expression, not the calculation item expression itself.
    • The overall purpose of my suggestions were to ensure the measure (with calculation items applied) returns numerical values, and the formatting is controlled solely by format strings.

     

    Sample screenshots:

     

MrRong's avatar
MrRong
Frequent Visitor
2 years ago

Hi OwenAuger ,

 

Thanks for helping me out.

 

I followed your instructions but it is still giving me the error.

 

1) Measure is updated:

 

 

2) PL_Total_Amount_Actual_2 measure a dynamic format string

 

 

3) Each Calculation Item have format string

 

 

 

 

 

The error in the Matrix visual is:

 

 

Do my Calculation Items have correct DAX measure?

 

I wonder that if we need a DAX in the Dynamic format string for the 3 Calculation Items?

 

 

Thanks for your help,

 

 

 

OwenAuger's avatar
OwenAuger
Super User
2 years ago

Hi again MrRong

 

I see Anonymous has also replied but I'll reply directly to your post. 

 

1. Measure looks good 🙂

2. The measure's dynamic format string looks good 🙂

3. The calculation items don't look right.

  • The calculation item expressions for Actual, Actual PY and Variance PY should remain unchanged from your original version, i.e. including SELECTEDMEASURE() within the expressions.
  • However, you should enable Dynamic format string for each calculation item and set it to SELECTEDMEASUREFORMATSTRING().
  • Generally speaking, it would only make sense to use SELECTEDMEASUREFORMATSTRING() within the Format string expression, not the calculation item expression itself.
  • The overall purpose of my suggestions were to ensure the measure (with calculation items applied) returns numerical values, and the formatting is controlled solely by format strings.

 

Sample screenshots:

 

  • MrRong's avatar
    MrRong
    Frequent Visitor
    2 years ago
    Hi Owen, You are spot on the solution. It is great how you troubleshoot the issue, it is very professional. Thanks again, Jimmy Truong