Dynamic Formatting with DirectLake Semantic Model

[ryan0585]

We've created a semantic model using DirectLake mode for the first time. In that semantic model, we're able to create measures that use dynamic formatting of the results. The use case is that we are allowing users to select metrics for a particular visual and the results can be in different formats. So, we have a single visual with a single measure to return the results, and the results are formatted dynamically. For measures created in the semantic model, this works just fine.

However, If I want to create a measure in a Power BI report (.pbix file) connected to that semantic layer, I can select Dynamic as the measure format, but when I do so one of two things happens. Either:

1) I cannot enter the dynamic formatting formula (input is not enabled when I switch from "Measure" for "Format" for the DAX)

2) When I am able to enter the DAX format string, it does not work, and when I navigate back to the "Format" DAX, input is disabled and the DAX is gone.

This is pretty limiting, as we do not want to create every single measure in our widely used semantic model - sometimes report writers need to be able to add measures locally and our intent is not to bloat the semantic model with DAX-calculated measures that serve only a single use case.

[MFelix]

Hi @ryan0585 ,

When you refer that you want Dynamic format string does the format that the users are doing should change accordingly to the context for example:

• Calculation is greater than 1 M then should return  the measure divide by 1M and then add a M to the value
• Creating a switch measure and then returning the corresponding format also based on a switch calculatio

Or is it just a custom string format for that specific value measure for example they want to add some letters or change the negative to be in parenthisis?

According to my test the dynamic format strings are getting the behaviour you refer however I'm able to make any format option written on the format text box:

[ryan0585]

Yeah so the second bullet there. We'd like to use the switch statement to evaluate the user's metric selection, then return a measure value in the appropriate format. Some sample DAX here we're using in the semantic model for the Format = "Dynamic" selection. Unfortunately, we just can't take this same approach ATM in a local .pbix file connected to that semantic model.

VAR SelMeasure = SELECTEDVALUE('metric'[metric_id])
RETURN
    SWITCH(
        SelMeasure,
        16, "#,0;-#,0;#,0",
        18, "#,0;-#,0;#,0",
        2, "0.0%;-0.0%;0.0%",
        3, "0.0%;-0.0%;0.0%",
        4, "0.0%;-0.0%;0.0%",
        5, "#,0;-#,0;#,0",
        47, "#,0;-#,0;#,0",
        6, "0.0%;-0.0%;0.0%",
        7, "0.0%;-0.0%;0.0%",
        8, "0.0%;-0.0%;0.0%"
    )

[MFelix · Accepted solution]

Hi @ryan0585 ,

Sorruy I should have check this sooner but the dynamic format strigs are not available for Report Measures not only for Direct Lake but for all semantic models in the service:

https://learn.microsoft.com/en-us/power-bi/create-reports/desktop-dynamic-format-strings

[ryan0585]

Got it, hopefully this enhancement is added in the future, or "Dynamic" as a formatting option is removed if it isn't to be an enhancement when dynamic formatting is our of preview mode. In its current implementation, when connected to a live semantic model, it does come off as more of a bug because you can select it as a formatting option but the behavior doesn't mirror the functionality of other connection methods. Thank you though.