Forum Discussion
Impactful Individualdynamic format string live connection issue
Hi powerbiexpert22,
Dynamic format strings need to be defined in the semantic model, so you can't normally add them directly when using a live connection.
If you have access to edit the original semantic model, I would recommend making the changes there. Your report can then remain live-connected, and other reports using the same model can benefit from the changes too.
If you want to work locally, you can open the downloaded PBIX, make the changes, and publish the updated semantic model back to Power BI Service. Just be careful not to overwrite the existing model unintentionally.
Another option is to select “Make changes to this model” in Power BI Desktop. This creates a local model on top of the remote semantic model, but it doesn't convert the connection to Import mode, and it may not overcome the dynamic format string limitation.
For reference, see Microsoft's documentation: https://learn.microsoft.com/en-us/power-bi/create-reports/desktop-dynamic-format-strings
Continued ContributorHi powerbiexpert22,
To answer the "how do I repoint" part directly: the cleanest way is to save the report as a Power BI Project (PBIP) and change the dataset reference in one file. No rebuilding of visuals is needed.
live-connected report -> File > Save as > Power BI Project (.pbip)
-> open <Report>.Report\definition.pbir in a text editor
-> swap "byConnection" (Service model) for "byPath" (local model)
-> put the downloaded model next to it as <Model>.SemanticModel
-> reopen the .pbip -> report now runs on the local model{
"version": "4.0",
"datasetReference": {
"byPath": {
"path": "../Sales.SemanticModel"
}
}
}The easy way to get the .SemanticModel folder is to open the downloaded PBIX and save it as PBIP too. The visuals keep working as long as table, column and measure names match.
That said, I would only go local if you have to. If the goal is just the format string, keeping one shared model is better long term. Two ways to add it there without touching the report:
edit the model in the Service (Open data model / web modeling)
-> select the measure -> Format: Dynamic -> add the expression
XMLA endpoint (Premium / Fabric capacity) + Tabular Editor
-> set the measure's Format String Expression -> save-- Format String Expression on the measure
SWITCH (
TRUE (),
ABS ( [Sales] ) >= 1e9, "#,0.0,,,\"B\"",
ABS ( [Sales] ) >= 1e6, "#,0.0,,\"M\"",
ABS ( [Sales] ) >= 1e3, "#,0.0,\"K\"",
"#,0"
)Avoid the FORMAT() measure workaround on a live connection: it returns text, so the values can't be used on chart axes and won't sort numerically.
So my overall approach would be:
can edit the shared model? -> add the dynamic format string there
-> report stays live, every report on the model benefits
must work locally? -> save as PBIP -> definition.pbir byPath
-> point it at the downloaded model -> add the format stringAI-assisted drafting: AI was used to help structure and phrase this response. I reviewed and validated the technical content before posting.