Answered

How does the table binding (or JSON filter string) work in the new Power BI Report component

(0) Share

Report

Posted on by rezaniroomand

I'm evaluating the new Power BI integration which is at the time of this writing "in preview". The overall integration works fine. I can add reports and datasets to the solution. It's even smart enough to add the datasets of the reports automatically. The only (major) problem I have is to bind the report to the current table.

As you can see in the screenshot below, the Power BI Report component has a checkbox called "Bind to table column". When selected, I assume (because there's absolutely no documentation that I could find), a drop-down is supposed to display a list of available columns to choose from, to be used as (again I have to guess) filter or maybe a report parameter. The drop-down is always empty and there is a text in it, saying "There are no table columns associated with the selected table available for this property.".

I also tried the JSON filtering approach with the following JSON with no luck.

{
"Filter": "[{\"$schema\":\"basic\",\"target\":{\"table\":\"Sales\",\"column\":\"accountid\"},\"operator\":\"In\",\"values\":[$a],\"filterType\":1}]",
"Alias": { "$a": "accountid" }
}

In the above snippet, "Sales" is a table in Power BI report fetched from an external data source (joined with Account table in Dataverse) and "accountid" is a column in it. The Power BI report is in a table in model-driven form of Account table.

What is it that I'm doing wrong?

PS. I tried adding the "accountid" to report's filter. The filter works if I use it within Power BI or in the filter pane of the Power BI component in the form, but still the filter JSON has no effect.

Categories:

Building Power Apps

I have the same question (0)

All responses (7)

Answers (1)

Random1231 6 on at

Like (0)

Report
Copy link

Link copied!

w

Was this reply helpful? Yes No
Verified answer

rezaniroomand 34 on at

Like (3)

Report
Copy link

Link copied!
I figured out why the JSON filtering approach didn't work and fixed it, but still the binding doesn't work. I list here some of the pitfall so future readers can avoid them.

If you put the whole JSON string in one line it will work.
If you add line breaks in the real JSON part it will work.
If you add any line break in the encoded part of the JSON string, it will fail. You can notice the filter in design mode, before publishing because data will be displayed without any filtering applied and no error.
If you make any misspelling the table name or column name an exclamation mark will be displayed beside the filter. This is why I recommend always displaying the filter pane and expanding it when in design mode.

Examples:
1. The entire string in one line:
```
{"Filter": "[{\"$schema\":\"basic\",\"target\":{\"table\":\"Sales\",\"column\":\"Nav_ID\"},\"operator\":\"In\",\"values\":[$a],\"filterType\":1}]", "Alias": {"$a": "ath_s_id_billing_navision"}}
```

2. Line breaks in the real JSON part:
```
{
"Filter": "[{\"$schema\":\"basic\",\"target\":{\"table\":\"Sales\",\"column\":\"Nav_ID\"},\"operator\":\"In\",\"values\":[$a],\"filterType\":1}]",
"Alias": {"$a": "ath_s_id_billing_navision"}
}
```

3. Line breaks in the encoded part of the JSON (WRONG).
```
{"Filter": "[{
\"$schema\":\"basic\",
\"target\":{\"table\":\"Sales\",\"column\":\"Nav_ID\"},\"operator\":\"In\",\"values\":[$a],\"filterType\":1
}]", "Alias": {"$a": "ath_s_id_billing_navision"}}
```

Was this reply helpful? Yes No
JsonString 4 on at

Like (1)

Report
Copy link

Link copied!

Hello rezaniroomand!
How did you resolve this issue? I've struggled with that a few days already and still no success.
- should there be a relationship between Power BI report and Dataverse table? If yes, then how to create it? There's no such a field in PowerBI report table stored in dataverse.
- what field should be used as a filter from datsverse table? Primary key or any field? Should we use display name or schema name in json string?
- what about case sensitivity and spaces in json string? If e.g. report name is "Sales Report", then should I put "sales_report" instead?
- what designe mode you use? I just go to Tables>Forms>edit form and I can't see any exclamation marks there. It just underlines column names with red color and some semicolons with blue color. I can't use a filter pane in this mode.

Was this reply helpful? Yes No
bromero 4 on at

Like (0)

Report
Copy link

Link copied!
Hi
I have been testing this (still in preview) feature for a couple days with no success. I can embed the report but the contextual filter is not working. Did you manage to make it work? I become no error and the report shows in my form. The filter context is just not being applied. This is my json string:

{"Filter": "[{"$schema\":\"basic\",\"target\":{\"table\":\"Donor",\"column\":\"Donor ID"},\"operator\":\"In\",\"values\":[$a],\"filterType\":1}]","Alias": {"$a": "contactid"}}

thanks for any help

Was this reply helpful? Yes No
rezanir 3 on at

Like (0)

Report
Copy link

Link copied!

Hi @bromero ,
You can see an example here: Embed a Power BI report in a model-driven app main form - Power Apps | Microsoft Learn
To make it work, you will need to have table name and its column in your Power BI report. Take extra care to get the spelling and casing right.

Was this reply helpful? Yes No
bromero 4 on at

Like (0)

Report
Copy link

Link copied!

Thanks @rezanir. I was missing a few "\" in my code. it now works. I did it the old way, though. As described here: Embed filtered Power BI reports on Dynamics 365 forms – again – the official way | crm chart guy

I don't like how in the new method a new Workspace with a copy of the dataset are required. I would most probably need to apply changes to both of them.

Was this reply helpful? Yes No
LauraGB 71 on at

Like (0)

Report
Copy link

Link copied!
I assume by now you've solved this but for future readers you've missed an \ before the closing " on Donor ID

{"Filter": "[{"$schema\":\"basic\",\"target\":{\"table\":\"Donor",\"column\":\"Donor ID\"},\"operator\":\"In\",\"values\":[$a],\"filterType\":1}]","Alias": {"$a": "contactid"}}

Was this reply helpful? Yes No