Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
MrRong
Frequent Visitor

Selectedmeasureformatstring DAX Calculation Item

 

Hi everyone,

 

I ran into an error while trying to create a Calculation Group.

 

I have a Measure:

MrRong_1-1706937439158.png

 

As you can see, the data type depends on the CalcType.

 

I created a Test calculation group:

 

MrRong_2-1706937548895.png

 

With Calculation items:

1) Actual

 

MrRong_3-1706937591311.png

 

2) Actual PY

 

MrRong_4-1706937618557.png

 

3) Variance PY

 

MrRong_5-1706937685408.png

 

 

The Matrix visual works fine with Actual and Actual PY.

However, as soon as I created the Variance PY, the visual has this error:

 

MrRong_6-1706937779337.png

 

It relates to the CalcType=3, FORMAT([PL_%_of_Revenue_Actual],"0.00%")

 

I have been trying to troubleshoot the error. The possible solution is Selectedmeasureformatstring DAX measure in the Dynamic Format String. 

I am unable how to apply to my Calculation Item measure.

Can someone please help?

 

 

MrRong_7-1706937953027.png

 

Thanks very much

2 ACCEPTED SOLUTIONS
Anonymous
Not applicable

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


 

View solution in original post

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:

OwenAuger_0-1707126680962.png

OwenAuger_0-1707127127636.png

 


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
LinkedIn

View solution in original post

6 REPLIES 6
Anonymous
Not applicable

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


 

Thank you Albert He

MrRong
Frequent Visitor

Hi @OwenAuger ,

 

Thanks for helping me out.

 

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

 

1) Measure is updated:

 

MrRong_0-1707042879321.png

 

2) PL_Total_Amount_Actual_2 measure a dynamic format string

 

MrRong_1-1707042981750.png

 

3) Each Calculation Item have format string

 

MrRong_2-1707043055134.png

 

MrRong_3-1707043090587.png

 

MrRong_4-1707043132230.png

 

 

The error in the Matrix visual is:

 

MrRong_5-1707043175778.png

 

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,

 

 

 

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:

OwenAuger_0-1707126680962.png

OwenAuger_0-1707127127636.png

 


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
LinkedIn

Hi Owen, You are spot on the solution. It is great how you troubleshoot the issue, it is very professional. Thanks again, Jimmy Truong
OwenAuger
Super User
Super User

Hi @MrRong 

Yes, I believe you're correct in identifying the issue, and part of the solution (dynamic format string) 🙂

The issue is:

  1. PL_Total_Amount_Actual_2 returns a text value for CalcType = 3 or CalcType =4.
  2. In this case, when the calculation item "Variance PY" is applied to this measure, the text values containing the "%" character cannot be cast as numbers, so an error is returned.

My recommendation would be to:

1. Adjust the PL_Total_Amount_Actual_2 measure so that it always returns a numerical value:

 

PL_Total_Amount_Actual_2 =
VAR CalcType =
    SELECTEDVALUE ( PL_header[CalcType] )
VAR DisplayDetailCode =
    SELECTEDVALUE ( PL_header[Detail] )
VAR isSubHeaderVisible =
    ISFILTERED ( PL_accounts[Subheader] )
VAR Result =
    SWITCH (
        TRUE (),
        isSubHeaderVisible = TRUE ()
            && DisplayDetailCode = 0, BLANK (),
        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]
    )
RETURN
    Result

 

2. Give the PL_Total_Amount_Actual_2 measure a dynamic format string similar to this:

 

VAR DefaultFormat = "#,##0;(#,##0);-" -- change as needed
VAR PercentageFormat = "0.00%"
VAR CalcType =
	SELECTEDVALUE ( PL_header[CalcType] )
VAR DisplayDetailCode =
	SELECTEDVALUE ( PL_header[Detail] )
VAR isSubHeaderVisible =
	ISFILTERED ( PL_accounts[Subheader] )
VAR Result =
	SWITCH (
		TRUE (),
		-- This first condition can be omitted if you like, since measure is blank
		isSubHeaderVisible = TRUE () && DisplayDetailCode = 0, BLANK (),
		CalcType IN { 1, 2 }, DefaultFormat,
		CalcType IN { 3, 4 }, PercentageFormat
	)
RETURN
	Result

 

3.  For the calculation group, ensure that each calculation item has format string expression

 

SELECTEDMEASUREFORMATSTRING ()

 

 

Does this fix it for you?

Please post back if needed.

 

Regards


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
LinkedIn

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.