Calculation group expression strings vs Measure dynamic strings

[Dellis81]

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
    FormatString

If 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!

[v-sdhruv]

Hi @Dellis81 ,
Since we didnt hear back, we would be closing this thread.
If you need any assistance, feel free to reach out by creating a new post.

Thank you for using Microsoft Community Forum

[v-sdhruv]

Hi @Dellis81 ,

Just wanted to check if you got a chance to review the suggestion provided and whether that helped you resolve your query?

[v-sdhruv]

Hi @Dellis81 ,

Just wanted to check if you got a chance to review the suggestion provided and whether that helped you resolve your query?

Thank You @wardy912   and @johnt75  for your prompt response to the query.

[Dellis81]

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!

[wardy912]

Hi @Dellis81 

 Could you create a disconnected table to help with the descriptions from other tables, then use TREATAS rather than LOOKUPVALUE?

 The format string will be correct as it's a simple measure.

[Dellis81]

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

[wardy912]

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 Label

If 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!

[Dellis81]

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!

[johnt75]

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
    FormatString

[Dellis81]

Thank 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!