Values not populating properly in Visuals

[Sidhant]

Hi everyone,
So I was working on creation of Power BI Project through automation (by reading Metadata from one of the extracted files), so I am able to create the schema, visuals as well. But there few minor issues which are as follows:
1. (For Measures: As a good practice I have created a separate table named '__Measures' wherein I have created all the measures)

So as you can in the above card visual there is maesure (named ProfitRatio) which is used but it does not display the data and here is the config structure in (report.json)

"config": "{\"name\":\"511a06b0-b6f9-4162-8bf5-1c131e6d3a7e\",\"layouts\":[{\"id\":0,\"position\":{\"x\":801,\"y\":42.1,\"z\":3000,\"width\":228.31,\"height\":98.77,\"tabOrder\":1000}}],\"singleVisual\":{\"visualType\":\"card\",\"projections\":{\"Values\":[{\"queryRef\":\"__Measures.ProfitRatio\"}]},\"prototypeQuery\":{\"Version\":2,\"From\":[{\"Name\":\"__Measures\",\"Entity\":\"__Measures\",\"Type\":0}],\"Select\":[{\"Column\":{\"Expression\":{\"SourceRef\":{\"Source\":\"__Measures\"}},\"Property\":\"ProfitRatio\"},\"Name\":\"__Measures.ProfitRatio\",\"NativeReferenceName\":\"ProfitRatio\"}]},\"drillFilterOtherVisuals\":true,\"hasDefaultSort\":true,\"objects\":{},\"vcObjects\":{\"title\":[{\"properties\":{\"text\":{\"expr\":{\"Literal\":{\"Value\":\"'Card Visual 2'\"}}}}}]}}}"
So then I removed the column and then re-added then it showed value but it was gibberish (not in the expected format)

(and even in the config there weren't that many changes)

But when I removed the measure from the __Measures table and created a new one manually (with the same expression) then it started to show the expected value, why is that the case?

(Even the config {report.json} I didn't find any changes from the previous one), is it something w.r.t annotations block?
Similarly I faced some issues with few visuals :
2. Another card wherein the column (Discount) was being populated with : It initially displayed blank

Structure (config) for it looked as follows:

Similarly here when I removed it and added it back it started displaying the value on the card visual:

And the other weired thing that I noticed even though I had selected the aggregation from sum to average in the config I could still see it as 'Sum'

2. Then the next issues was with the Calculated columns: So I had 2 calculated columns (2022 Sales, 2023 Sales) ideally I was expecting them to be populated properly since in the data model I could see them and there were no issues

(But I could only see only 2 columns being selected epected were 3), but in the report.json I could see the three columns and the respective visual

So when I removed the visual and re-added it with the same columns it displayed the data:

3. Similar kind of issue I observed on another visual: clustered bar chart wherein on one of the axes I had an Hierachy column and on the other axes a normal column, still it was not displaying/visualizing the data

(In report.json it looked like)

So here as well when I removed the hierarchy column and re-added it: It started showcasing the data

After dragging (manually) the column all the respective hierarchies were displayed and in report.json there was a subtle difference instead of just the traditional TableName.ColumnName the structure was TableName.ColumnName.ColumnInHierarchy

So in the Metadata extract I did make use of similar format and added 3 rows:
X Axis orders.Category heir.Category; X Axis orders.Category heir.Sub-Category but that created a duplicate of the Category Hier column (which was giving an issue in first place). 

Just needed some inputs on these issues how can these be resolved. If any additional information is needed do let me know I will be happy to share that.

Regards,
Sidhant

[v-prasare]

Hi @Sidhant ,

We would like to follow up to see if your query got resolved? Please let us know if you need any further assistance.

Thanks,

Prashanth Are

MS Fabric community support

[Sidhant]

Hi @v-prasare ,
Yes the query is resolved, I have accepted my response as answer.

Regards,
Sidhant.

[v-prasare]

Hi @Sidhant,

Thanks for actively participating MS Fabric community support

We would like to follow up to see if the solution provided by the super user resolved your issue. Please let us know if you need any further assistance.

@MFelix & @wardy912, thanks for your prompt response.

Thanks,

Prashanth Are

MS Fabric community support

If our super user response resolved your issue, please mark it as "Accept as solution" this will help other user to find similar answers easily

[Sidhant]

Hi @MFelix , @wardy912 
So I was able to fix one issue which was with regarding to the measures (regarding the gibberish value) turns out it was due to the 'annotations' block was missing:

So I added this block and appended to the maeasures block whenever it was being created. So once I opened the Power BI Project file at start even though the column was populated I could see the warning message but when I removed it and added it back it displayed correct value

Now there are only two issues which I'm still able to fix one is the 'Hierarchy' so I did try whta @wardy912 had posted as per the format: Table_name.Hierarchy_name.Column/Level_name (followed this in the metadata)

Then even tried to provide a separate entry for each column

"config": "{\"name\": \"eb7a88c7-f9ed-4b33-a415-e6778c816735\", \"layouts\": [{\"id\": 0, \"position\": {\"x\": 15.12, \"y\": 28.35, \"z\": 0.0, \"width\": 1234.0, \"height\": 279.0, \"tabOrder\": 0}}], \"singleVisual\": {\"visualType\": \"clusteredBarChart\", \"projections\": {\"Category\": [{\"queryRef\": \"orders.Category heir.Category\"}, {\"queryRef\": \"orders.Category heir.Sub-Category\"}, {\"queryRef\": \"orders.Category heir.Segment\"}], \"Y\": [{\"queryRef\": \"Sum(orders.Sales)\"}]}, \"prototypeQuery\": {\"Version\": 2, \"From\": [{\"Name\": \"orders\", \"Entity\": \"orders\", \"Type\": 0}], \"Select\": [{\"Column\": {\"Expression\": {\"SourceRef\": {\"Source\": \"orders\"}}, \"Property\": \"Category heir.Category\"}, \"Name\": \"orders.Category heir.Category\", \"NativeReferenceName\": \"Category heir.Category\"}, {\"Column\": {\"Expression\": {\"SourceRef\": {\"Source\": \"orders\"}}, \"Property\": \"Category heir.Sub-Category\"}, \"Name\": \"orders.Category heir.Sub-Category\", \"NativeReferenceName\": \"Category heir.Sub-Category\"}, {\"Column\": {\"Expression\": {\"SourceRef\": {\"Source\": \"orders\"}}, \"Property\": \"Category heir.Segment\"}, \"Name\": \"orders.Category heir.Segment\", \"NativeReferenceName\": \"Category heir.Segment\"}, {\"Aggregation\": {\"Expression\": {\"Column\": {\"Expression\": {\"SourceRef\": {\"Source\": \"orders\"}}, \"Property\": \"Sales\"}}, \"Function\": 0}, \"Name\": \"Sum(orders.Sales)\", \"NativeReferenceName\": \"Sum of Sales\"}]}, \"drillFilterOtherVisuals\": true, \"hasDefaultSort\": true, \"objects\": {\"dataPoint\": [{\"properties\": {\"fill\": {\"solid\": {\"color\": {\"expr\": {\"Literal\": {\"Value\": \"'#6590B6'\"}}}}}, \"fontSize\": {\"expr\": {\"Literal\": {\"Value\": \"30D\"}}}}}]}, \"vcObjects\": {\"title\": [{\"properties\": {\"text\": {\"expr\": {\"Literal\": {\"Value\": \"'Clusteredbarchart Visual 15'\"}}}}}]}}}"

This was quite similar to the one that was working (wherein I manually dragged the hierachy column but even this work out)
Working one:

"config": "{\"name\":\"8a4c3cb3-c428-4604-9fdb-69508651ef09\",\"layouts\":[{\"id\":0,\"position\":{\"x\":15.12,\"y\":28.35,\"z\":0,\"width\":1234,\"height\":279,\"tabOrder\":1000}}],\"singleVisual\":{\"visualType\":\"clusteredBarChart\",\"projections\":{\"Y\":[{\"queryRef\":\"Sum(orders.Sales)\"}],\"Category\":[{\"queryRef\":\"orders.Category heir.Category\",\"active\":true},{\"queryRef\":\"orders.Category heir.Sub-Category\"},{\"queryRef\":\"orders.Category heir.Segment\"}]},\"prototypeQuery\":{\"Version\":2,\"From\":[{\"Name\":\"orders\",\"Entity\":\"orders\",\"Type\":0}],\"Select\":[{\"Aggregation\":{\"Expression\":{\"Column\":{\"Expression\":{\"SourceRef\":{\"Source\":\"orders\"}},\"Property\":\"Sales\"}},\"Function\":0},\"Name\":\"Sum(orders.Sales)\",\"NativeReferenceName\":\"Sum of Sales\"},{\"HierarchyLevel\":{\"Expression\":{\"Hierarchy\":{\"Expression\":{\"SourceRef\":{\"Source\":\"orders\"}},\"Hierarchy\":\"Category heir\"}},\"Level\":\"Category\"},\"Name\":\"orders.Category heir.Category\",\"NativeReferenceName\":\"Category heir Category\"},{\"HierarchyLevel\":{\"Expression\":{\"Hierarchy\":{\"Expression\":{\"SourceRef\":{\"Source\":\"orders\"}},\"Hierarchy\":\"Category heir\"}},\"Level\":\"Sub-Category\"},\"Name\":\"orders.Category heir.Sub-Category\",\"NativeReferenceName\":\"Category heir Sub-Category\"},{\"HierarchyLevel\":{\"Expression\":{\"Hierarchy\":{\"Expression\":{\"SourceRef\":{\"Source\":\"orders\"}},\"Hierarchy\":\"Category heir\"}},\"Level\":\"Segment\"},\"Name\":\"orders.Category heir.Segment\",\"NativeReferenceName\":\"Category heir Segment\"}],\"OrderBy\":[{\"Direction\":2,\"Expression\":{\"Aggregation\":{\"Expression\":{\"Column\":{\"Expression\":{\"SourceRef\":{\"Source\":\"orders\"}},\"Property\":\"Sales\"}},\"Function\":0}}}]},\"drillFilterOtherVisuals\":true,\"hasDefaultSort\":true,\"objects\":{\"dataPoint\":[{\"properties\":{\"fill\":{\"solid\":{\"color\":{\"expr\":{\"Literal\":{\"Value\":\"'#6590B6'\"}}}}}}}]},\"vcObjects\":{\"title\":[{\"properties\":{\"text\":{\"expr\":{\"Literal\":{\"Value\":\"'Clusteredbarchart Visual 15'\"}}}}}]}}}"

 
So if you guys have any inputs please do share them.

Regards,
Sidhant.

[Sidhant · Accepted solution]

Hi @MFelix and @wardy912 ,
I was able to identify the root cause for the 'Hierarchy'not populating as intended. The reason behind it was the missing 'HierarchLevel' which denotes the nested order. So I made few changes in my code and then I was able to see the expected o/p:

In the MetaData (added the 3 new entries which depict which all columns from the hierarchy are being used)

And for the measures displying 'gibberish' value, it was due to missing 'annotations' block and after adding that no longer got the gibberish value.

Identifying this small part did take a bit of time but in the end was able to resolve (debug) it.
Thanks for your initial response @MFelix  and @wardy912 

Regards,
Sidhant.

[MFelix]

Hi @Sidhant ,

Glad you were able to figure out the problem.

Don't forget to mark the correct answer to help out others, even if it's your own.

[Sidhant]

Hi @MFelix ,
Thanks, yes I will be marking my response as Solution, so that if others face similar issue they can check the answer.

Regards,
Sidhant.

[MFelix]

Hi @Sidhant ,

First of all I suggest that you turn on the new PBIR format on the preview this will change the format of your PBIP Json files to a much more readable way:

Has you can see there is a more strucured way of showing the values.-

One thing I know is that the report is based on locations and name of calculations / columns this is tricky because if you change a small thing in the naming or you add a folder whern you connect to that model you will get the break of your report (assuming you are separating the report from the semantic model).

On the question of the calculated columns be carefull because of the use of implicit measures they are always tricky to use because they have different sintax when looking at the report it changes the way it get pulled in.

Just ot give some more context can you please let me know if the differences you are having is after making changes to the Json Files? are they based on copy paste from one report to the other? what do you mean by by reading Metadata from one of the extracted files

[Sidhant]

Hi @MFelix ,
Thanks for the suggestion I will check the pbir format. Coming to your query: The thing is I have a MetaData extract (which is from one of a tableau report and we converted this metadata which gives out information what was teh source (postgres,snowflake), what tables were pulled from the source and the respective columns, were there any calculations (measures then they are converted into equivalent Power BI format), etc).
And coming to the second question differences (the images that I shared in my main query) there the config (you saw all of this generated by a script) i.e. automated fashion (which reads from the metadata file) and creates an equivalent Power BI Project file structure (wherein you have Power BI Project file {*.pbip}, Semantic Model: contains the model.bim file  and Report Folder: contains the report.json
I hope this answers your query.

Regards,
Sidhant.

[wardy912]

Hi @Sidhant 

 This is AI generated and unconfirmed, I hope it helps!

 1. Measures Not Displaying Correctly in __Measures Table

Issue Summary:

• Measures created in __Measures table via automation don’t display values until manually removed and re-added.
• Even when re-added, values are gibberish unless recreated manually.

Possible Causes & Fixes:

• Metadata Binding Issue: Power BI might not be correctly binding the measure to the visual due to how it's referenced in the report.json. Even if the syntax looks correct, internal bindings (GUIDs, internal IDs) might not be resolving.
• Missing Annotations or Dependencies: Measures might require specific metadata annotations or dependencies (like relationships or formatting) that are not being captured in your automation.
• Fix: Try ensuring:
  • The measure is fully defined in the model.bim or equivalent metadata file.
  • The report.json references the measure using the correct queryRef and NativeReferenceName.
  • Include format strings and data category annotations if applicable.

---

✅ 2. Card Visuals Not Displaying Columns (e.g., Discount)

Issue Summary:

• Card visuals show blank until the column is removed and re-added.
• Aggregation type mismatch (e.g., set to Average but shows Sum).

Possible Causes & Fixes:

• Stale Visual Metadata: The visual might be referencing an outdated or incorrect internal column ID.
• Aggregation Mismatch: The aggregation setting in the config might not be applied correctly unless the visual is refreshed.
• Fix:
  • Ensure the projections and prototypeQuery sections are in sync.
  • Double-check the aggregationFunction property in the visual config.
  • Consider triggering a refresh or rebind step in your automation after visuals are created.

---

✅ 3. Calculated Columns Not Displaying in Visuals

Issue Summary:

• Calculated columns exist in the model but don’t show up in visuals until manually re-added.

Possible Causes & Fixes:

• Model Sync Issue: The visual might be referencing a column before it’s fully available in the model.
• Fix:
  • Ensure calculated columns are fully processed and available in the model before visuals are created.
  • Add a validation step to confirm column availability before binding to visuals.

✅ 4. Hierarchy Columns Not Displaying Properly

Issue Summary:

• Hierarchy columns don’t render until manually re-added.
• JSON structure changes subtly when fixed manually.

Possible Causes & Fixes:

• Hierarchy Binding Format: Power BI expects hierarchy columns to be referenced in a specific format (Table.Hierarchy.Level).
• Duplicate Hierarchies: Adding hierarchy levels manually in metadata might cause duplication or incorrect binding.
• Fix:
  • Use the correct hierarchy structure in report.json:

"queryRef": "orders.Category heir.Category"

should be:

"queryRef": "orders.Category heir.Category.Category"

• • Avoid manually duplicating hierarchy levels in metadata. Instead, define the hierarchy once and reference it properly.

---

🔧 General Recommendations

1. Validate Model Before Visual Binding:

   • Ensure all tables, columns, measures, and hierarchies are fully loaded and processed before visuals are created.

2. Use Power BI’s Internal IDs Where Possible:

   • Power BI uses internal IDs for binding visuals. If your automation skips these, visuals may not bind correctly.

3. Compare Working vs. Broken JSONs:

   • Use a diff tool to compare the report.json before and after manual fixes. Even subtle changes (like GUIDs or internal references) can be revealing.

4. Consider Using Tabular Editor Scripts:

   • For measures and calculated columns, using Tabular Editor scripts can ensure consistency and proper metadata generation.

5. Post-Processing Step:

   • After generating the report, run a post-processing script to validate and fix bindings, especially for visuals using calculated columns, measures, or hierarchies.

[Sidhant]

Hi @wardy912 ,
Thanks for sharing I will check this, not sure whether it works or not but will check that. Meanwhile if you anything to add up please do share.

Regards,
Sidhant.