Forum Discussion
Calculation group expression strings vs Measure dynamic strings
Hello
I am attempting to implement dymanic format strings into calculation groups. For starters - I have a select few measures utilizing dynamic format strings - which are working great. However, I want to take those same measures and push into a calculation group. Unfortunately the format does not seem to work.
In the below example, I have added a calculation group item that displays the same dax code as used in the quantity expression string. Note the the last characters are "19" vs my desired "GAL"
Below is the dax code that is being used as both "Quantity" dynamic format and "QuantityFormat" item.
VAR BaseFormat =
COALESCE ( SELECTEDMEASUREFORMATSTRING (), "#,#.00" )
VAR Label = " "&[ItemPurUnit]
VAR FormatString = BaseFormat&label
RETURN
FormatStringIf I was to substitute the var "label" with a hardcoded " GAL" then it displays properly.
Why am I getting the "19" as my final two characters?
PS - I have based this logic from an SQLBI youtube video
Thank you! This forum has always been great to me!
10 Replies
I had a similar issue recently, it could be that the 0s and .s need to be escaped. Try
VAR BaseFormat = COALESCE ( SELECTEDMEASUREFORMATSTRING (), "#,#.00" ) VAR Label = " " & [ItemPurUnit] VAR FormatString = SUBSTITUTE ( SUBSTITUTE ( BaseFormat & label, "0", "\0" ), ".", "\." ) RETURN FormatStringThank you for your response!
I tried your suggestion, but getting something a little different. Rounding up, but the additional characters reflect the initial measure value. I'm not sure what you mean characters need to be escaped?
thank you!
Hi Dellis81
[ItemPurUnit] might not be resolving correctly in the calculation group — it could be pulling from a different row context or returning a numeric value (like 19) instead of the expected unit label.
Try this simplified version in your calculation group to test the theory
VAR Label = " " & SELECTEDVALUE([ItemPurUnit], "N/A") RETURN LabelIf this returns "19" again, then we know the issue is [ItemPurUnit].
If it is, then try using LOOKUPVALUE to explicitly find the value.
Replace 'YourTable' and [ItemKey] with your actual table and key column.
VAR BaseFormat = COALESCE(SELECTEDMEASUREFORMATSTRING(), "#,#.00") VAR Label = " " & LOOKUPVALUE('YourTable'[ItemPurUnit], 'YourTable'[ItemKey], SELECTEDVALUE('YourTable'[ItemKey])) VAR FormatString = BaseFormat & Label RETURN FormatString--------------------------------
I hope this helps, please give kudos and mark as solved if it does!
Connect with me on LinkedIn.
Subscribe to my YouTube channel for Fabric/Power Platform related content!
Ok, I think you are onto something. Here's a screenshot of what I get with your 1st suggestion.
Notice I did get the NA. My challenge tho - the [ItemPurUnit] is a rather complicated measure - pulling unit descriptors from several tables. A lookup will not resolve?
If it is related to a calculation group conflict - why inserting the DAX format expression string as a CG item resolve correctly? As you can see - the QuantityFormat item is resulting in the desired format string?
thank you!
Possibly - let me see how that might look. I am travelling next few days, might be this weekend before I get back to you. Thank you
Thank you for the followup - I've worked on briefly past few days. I'm in the process of creating a disconnected table - I have three types of unit descriptors - Unit1, Unit2, and then a Unit2/Unit1 rate that I am still trying to make sense of.
Unfortunately - I am travelling Wed/Thur of this week - but will keep pushing on as I can.
Thank you ALL for assistance!
