Solved: Trouble applying filters to Power BI report embedd...

shravanighosh · ‎08-25-2023

I am trying to embed a Power BI report into my Laravel project using the Power BI REST API. The report is loading successfully. However, I am facing issues with applying filters to the report, specifically using a column named 'Partner Name' from the Excel dataset as a filter. I am also including my library's importing code.

Expected Behavior: I expect the Power BI report to filter data based on the 'Partner Name' column when the report is loaded.

Actual Behavior: The report loads successfully, but the filtering is not applied as expected.

     <head>
    <script src="https://microsoft.github.io/PowerBI-JavaScript/demo/node_modules/jquery/dist/jquery.js"></script>
    <script src="https://microsoft.github.io/PowerBI-JavaScript/demo/node_modules/powerbi-client/dist/powerbi.js"></script>
    <script src="https://cdnjs.cloudflare.com/ajax/libs/powerbi-client/2.22.4/powerbi.js"></script>
    <script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.7.0/jquery.min.js" integrity="sha512-3gJwYpMe3QewGELv8k/BX9vcqhryRdzRMxVfq6ngyWXwo03GFEzjsUm8Q7RZcHPHksttq7/GFoxjCVUjkjvPdw==" crossorigin="anonymous" referrerpolicy="no-referrer"></script>
    <script src="https://cdnjs.cloudflare.com/ajax/libs/powerbi-client/2.22.4/powerbi.min.js"></script>
    <script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.7.0/jquery.min.js" integrity="sha512-3gJwYpMe3QewGELv8k/BX9vcqhryRdzRMxVfq6ngyWXwo03GFEzjsUm8Q7RZcHPHksttq7/GFoxjCVUjkjvPdw==" crossorigin="anonymous" referrerpolicy="no-referrer"></script>
    <script src="https://cdn.jsdelivr.net/npm/powerbi-models@1.12.6/dist/models.min.js"></script>
    
    </head>
    <body>



<div id="dashboardContainer"></div>

<script type="text/javascript">

function test() {
             const basicFilter = {
                    $schema: "http://powerbi.com/product/schema#filter",
                    target: {
                        table: "WBL-AllReviews-Export Less Records",        //excel sheet used for dataset
                        column: "Partner name"
                    },
                    operator: "Is",
                    values: ["WBL Test", "ELCC"],
                    filterType: models.FilterType.BasicFilter, //also tried powerbi.Filt...
                    requireSingleSelection: true
                };
            var embedConfiguration = {
            type: 'report',
            accessToken: 'xxxxxxxxxxxx-access-token-xxxxxxxxxx',
            embedUrl: 'https://app.powerbi.com/reportEmbed?reportId=4asdfxxxxxxxxxxxxxx&groupId=5ussrxxxxxxxxxxxxxxxxxxxxxxx',
            id:'4asdfxxxxxxxxxxxxxx',
            settings: {
                filterPaneEnabled: true, 
                navContentPaneEnabled: true
            },
            filters: [
                    basicFilter
                ]
            };

            var $reportContainer = $('#dashboardContainer');
            var report = powerbi.embed($reportContainer.get(0), embedConfiguration);
    }


   
   function reloadreport(){
         var element = $('#dashboardContainer');
         alert(element);
         var report = powerbi.get(element);
         report.reload().catch(error => {console.log(error) });
   };
    
setTimeout(test, 5000);

            </script>

v-yueyunzh-msft · ‎08-28-2023

Hi , @shravanighosh

Thanks for your quick response ! Sorry for that i am not a front-end engineer .. My code is just copy from the Power BI Embed playground .You can also test it in this .

Power BI Playground

But for your need , i can suggest you to check this :
(1)You can first make sure the filter code can work in your embed report .

You can use the filter report page code in your "Console" in your browser.

const filter3 = {
            $schema: "http://powerbi.com/product/schema#basic",
            target: {
                table: "Alphabetical_list_of_products",
                column: "CategoryName"
            },
            operator: "In",
            values: ["Produce", "Seafood"]
        };
 report.updateFilters(models.FiltersOperations.Add, [filter2]);

For this , you can test the code is correct or not and you can also check the version for your .js file.

This step you can also check in the Power BI Embed playground . And if do not work , you may need to check this document:
Filter a report using query string parameters in the URL - Power BI | Microsoft Learn

(2)For thsi step is need to execute after the report has rendered , so you need to use the await in the front-end to control the code load i think.

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI!

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

View solution in original post

shravanighosh · ‎08-28-2023

thank you for providing the initial code snippet. I tried using it, but I encountered some issues. Here's the code snippet I used:

<head>
<script src="https://microsoft.github.io/PowerBI-JavaScript/demo/node_modules/jquery/dist/jquery.js"></script>
<script src="https://microsoft.github.io/PowerBI-JavaScript/demo/node_modules/powerbi-client/dist/powerbi.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/powerbi-client/2.22.4/powerbi.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.7.0/jquery.min.js" integrity="sha512-3gJwYpMe3QewGELv8k/BX9vcqhryRdzRMxVfq6ngyWXwo03GFEzjsUm8Q7RZcHPHksttq7/GFoxjCVUjkjvPdw==" crossorigin="anonymous" referrerpolicy="no-referrer"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/powerbi-client/2.22.4/powerbi.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.7.0/jquery.min.js" integrity="sha512-3gJwYpMe3QewGELv8k/BX9vcqhryRdzRMxVfq6ngyWXwo03GFEzjsUm8Q7RZcHPHksttq7/GFoxjCVUjkjvPdw==" crossorigin="anonymous" referrerpolicy="no-referrer"></script>
<script src="https://cdn.jsdelivr.net/npm/powerbi-models@1.12.6/dist/models.min.js"></script>
<script src="https://code.jquery.com/jquery-3.6.0.min.js"></script>
<script src="https://cdn.jsdelivr.net/npm/powerbi-models@1.12.6/dist/models.min.js"></script>
</head>
<body>
<div id="embedContainer"></div>
<script>

    let loadedResolve, reportLoaded = new Promise((res, rej) => { loadedResolve = res; });
    let renderedResolve, reportRendered = new Promise((res, rej) => { renderedResolve = res; });
    const tokenType = '0';
    let report;
    const models = window['powerbi-client'].models;

    async function embedPowerBIReport() {
        let accessToken = 'eyKoxxxUxIx3RNLWzQ';
        let embedUrl = 'https://app.powerbi.com/reportEmbed?reportId=4efadffadsffadsffadfasdfb292-6a53195f671f&config=eyxxxxxxxxxxxxxxxxxxxxxxxxxxxxJlZCI6dHJ1ZSwidXNhZ2VNZXRyaWNzVk5leHQiOnRydWV9fQ%3d%3d';
        let embedReportId = '4ef9a26asdfadsfadfasdfasadfa';

        const config = {
            type: 'report',
            tokenType: tokenType == '0' ? models.TokenType.Aad : models.TokenType.Embed,
            accessToken: accessToken,
            embedUrl: embedUrl,
            id: embedReportId,
            permissions: models.Permissions.All,
            settings: {
                filterPaneEnabled: true,
                navContentPaneEnabled: true
            }
        };

        let embedContainer = $('#embedContainer')[0];

        report = powerbi.embed(embedContainer, config);

        report.off("loaded");

        report.on("loaded", function () {
            loadedResolve();
            report.off("loaded");
        });

        report.off("error");

        report.on("error", function (event) {
            console.log(event.detail);
        });

        report.off("rendered");

        report.on("rendered", function () {
            renderedResolve();
            report.off("rendered");
        });

       
    }

     embedPowerBIReport();

        const filter2 = {
            $schema: "http://powerbi.com/product/schema#basic",
            target: {
                table: "WBL-AllReviews-Export Less Records.csv",
                column: "Partner name"
            },
            operator: "In",
            values: ["WBL-Test"]
        };

    async function fn3() {
        setTimeout(() => {
            report.updateFilters(models.FiltersOperations.Add, [filter2])
        })
    }

    async function fn() {
        try {
            const res1 = await reportLoaded
            console.log('reportLoaded：' + res1)
            const res2 = await reportRendered
            console.log('reportRendered：' + res2)
            const res3 = await fn3()
            console.log('fn3()：' + res3)
        } catch (error) {
            console.log('Error: ' + error)
        }
    }
    fn()
    

</script>

The above code cause errors like,

reportLoaded：undefined
powerbi:131 reportRendered：undefined
powerbi:133 fn3()：undefined

I also tried a different approach using the following code snippet. This is not throwing any errors but still the filtering part isn't working:

<script>
        let loadedResolve, reportLoaded = new Promise((res, rej) => { loadedResolve = res; });
        let renderedResolve, reportRendered = new Promise((res, rej) => { renderedResolve = res; });
        let report;
        const models = window['powerbi-client'].models;

        function embedPowerBIReport() {
            const accessToken = 'eyJeXAiOxxxxxxxxxxxxxx';
            const embedUrl = 'https://app.powerbi.com/reportEmbed?reportId=4efxxxxxxx-xxxxxd%3dd%3d';
            const embedReportId = '4efxxxxxxxxxxxxxxxxxxxxxx';

            const tokenType = '0';

            const permissions = models.Permissions.All;

            const config = {
                type: 'report',
                tokenType: tokenType == '0' ? models.TokenType.Aad : models.TokenType.Embed,
                accessToken: accessToken,
                embedUrl: embedUrl,
                id: embedReportId,
                permissions: permissions,
                settings: {
                    panes: {
                        filters: {
                            visible: true
                        },
                        pageNavigation: {
                            visible: true
                        }
                    }
                }
            };

            const embedContainer = $('#embedContainer')[0];

             report = powerbi.embed(embedContainer, config);

            report.off("loaded");
            report.on("loaded", function () {
                loadedResolve();
                report.off("loaded");
            });

            report.off("error");
            report.on("error", function (event) {
                console.log(event.detail);
            });

            report.off("rendered");
            report.on("rendered", function () {
                renderedResolve();
                report.off("rendered");
            });
        }
        async function applyFilter() {
            alert("dsfa");
            const filter = {
                $schema: "http://powerbi.com/product/schema#basic",
                target: {
                    table: "WBL-AllReviews-Export Less Records.csv",
                    column: "Partner name"
                },
                operator: "In",
                values: ["WBL-Test"]
            };

            await reportLoaded;
            await reportRendered;

            report.updateFilters(models.FiltersOperations.Add, [filter]);
        }
        embedPowerBIReport();

        try {
            setTimeout(applyFilter);
            console.log("Report filter was added.");
        }
        catch (errors) {
            console.log(errors);
        }
</script>

I would greatly appreciate your assistance in troubleshooting these code snippets and getting them to work as intended. If you have any insights or suggestions, please let me know. Thank you!

v-yueyunzh-msft · ‎08-28-2023

Hi , @shravanighosh

Thanks for your quick response ! Sorry for that i am not a front-end engineer .. My code is just copy from the Power BI Embed playground .You can also test it in this .

Power BI Playground

But for your need , i can suggest you to check this :
(1)You can first make sure the filter code can work in your embed report .

You can use the filter report page code in your "Console" in your browser.

const filter3 = {
            $schema: "http://powerbi.com/product/schema#basic",
            target: {
                table: "Alphabetical_list_of_products",
                column: "CategoryName"
            },
            operator: "In",
            values: ["Produce", "Seafood"]
        };
 report.updateFilters(models.FiltersOperations.Add, [filter2]);

For this , you can test the code is correct or not and you can also check the version for your .js file.

This step you can also check in the Power BI Embed playground . And if do not work , you may need to check this document:
Filter a report using query string parameters in the URL - Power BI | Microsoft Learn

(2)For thsi step is need to execute after the report has rendered , so you need to use the await in the front-end to control the code load i think.

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI!

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

shravanighosh · ‎08-30-2023

Hi @v-yueyunzh-msft ,
Thank you for your quick response. I appreciate your time and efforts to solve this issue. Your solution worked for me. For any reference, I am posting my working code here.
Thanks again for your time.
Best Regards.
Shravani.

<div id="embedContainer"></div>
<script>
let loadedResolve, reportLoaded = new Promise((res, rej) => { loadedResolve = res; });
let renderedResolve, reportRendered = new Promise((res, rej) => { renderedResolve = res; });
let report;
const models = window['powerbi-client'].models;

function embedPowerBIReport() {
const accessToken = 'ACCESS_TOKEN';
const embedUrl = 'https://app.powerbi.com/reportEmbed?reportId=xxxxxxxxxxxxxxxxxxxxxxxxxxxx';
const embedReportId = 'REPORTID';

const tokenType = '0';

const permissions = models.Permissions.All;

const config = {
type: 'report',
tokenType: tokenType == '0' ? models.TokenType.Aad : models.TokenType.Embed,
accessToken: accessToken,
embedUrl: embedUrl,
id: embedReportId,
permissions: permissions,
settings: {
panes: {
filters: {
visible: true
},
pageNavigation: {
visible: true
}
}
}
};

const embedContainer = $('#embedContainer')[0];

report = powerbi.embed(embedContainer, config);

report.off("loaded");
report.on("loaded", function () {
loadedResolve();
report.off("loaded");
});

report.off("error");
report.on("error", function (event) {
console.log(event.detail);
});

report.off("rendered");
report.on("rendered", function () {
renderedResolve();
report.off("rendered");
});
}
embedPowerBIReport();
async function applyFilter() {
const filter = {
$schema: "http://powerbi.com/product/schema#basic",
target: {
table: "WBL-AllReviews-Export Less Records", //Get Table name: https://community.fabric.microsoft.com/t5/Power-Query/Power-Bi-query-or-formula-to-Get-Table-name/td-p/2888275
column: "Partner name"
},
operator: "In",
values: ["WBL-Central", "WBL-Harbour"]
};

await reportLoaded;
await reportRendered;

await report.updateFilters(models.FiltersOperations.Add, [filter]);
}

try {
setTimeout(applyFilter);
console.log("Report filter was added.");
}
catch (errors) {
console.log(errors);
}
</script>

v-yueyunzh-msft · ‎08-27-2023

HI , @shravanighosh

Accoridng to your description , you want to emebed report and filter on the page .

For the embed token , it seems there is no paramter for the "Filter" .

For this , you can refer to :
Embed Token - Generate Token - REST API (Power BI Power BI REST APIs) | Microsoft Learn

And you need to after the page have loaded and then do the filter action for your embed page .

And you can try the code in Power BI Embed Playground.

Power BI Playground - Developer Sandbox

And for this , you can also refer to and you also need to consider the special character in your url.

Filter a report using query string parameters in the URL - Power BI | Microsoft Learn

And you can also refer to my latest test code like this:

<script>

    let loadedResolve, reportLoaded = new Promise((res, rej) => { loadedResolve = res; });
    let renderedResolve, reportRendered = new Promise((res, rej) => { renderedResolve = res; });

    // Get models. models contains enums that can be used.
    models = window['powerbi-client'].models;

    // Embed a Power BI report in the given HTML element with the given configurations
    // Read more about how to embed a Power BI report in your application here: https://go.microsoft.com/fwlink/?linkid=2153590
    async function embedPowerBIReport() {
        /*-----------------------------------------------------------------------------------+
        |    Don't change these values here: access token, embed URL and report ID.          |
        |    To make changes to these values:                                                |
        |    1. Save any other code changes to a text editor, as these will be lost.         |
        |    2. Select 'Start over' from the ribbon.                                         |
        |    3. Select a report or use an embed token.                                       |
        +-----------------------------------------------------------------------------------*/
        // Read embed application token
         // Read embed application token from Model
        let accessToken = "@Model.EmbedToken.Token";

    // You can embed different reports as per your need by changing the index
    // Read embed URL from Model
        let embedUrl = "@Html.Raw(Model.EmbedReports[0].EmbedUrl)";

    // Read report Id from Model
        let embedReportId = "@Model.EmbedReports[0].ReportId";

   

        // Create the embed configuration object for the report
        // For more information see https://go.microsoft.com/fwlink/?linkid=2153590
        const config = {
            type: 'report',
            tokenType: models.TokenType.Embed,
            accessToken: accessToken,
            embedUrl: embedUrl,
            id: embedReportId,
            permissions: models.Permissions.All,
            settings: {
                // Enable this setting to remove gray shoulders from embedded report
                // background: models.BackgroundType.Transparent,
                filterPaneEnabled: true,
                navContentPaneEnabled: true
            }
        };

        // Get a reference to the embedded report HTML element
        let embedContainer = $('#embedContainer')[0];

        // Embed the report and display it within the div container.
         report = powerbi.embed(embedContainer, config);

     


        // report.off removes all event handlers for a specific event
        report.off("loaded");

        // report.on will add an event handler
        report.on("loaded", function () {
            loadedResolve();
            report.off("loaded");
        });

        // report.off removes all event handlers for a specific event
        report.off("error");

        report.on("error", function (event) {
            console.log(event.detail);
        });

        // report.off removes all event handlers for a specific event
        report.off("rendered");

        // report.on will add an event handler
        report.on("rendered", function () {
            renderedResolve();
            report.off("rendered");
        });

       
    }

     embedPowerBIReport();
    

    // Insert here the code you want to run after the report is loaded

    
   
    // Insert here the code you want to run after the report is rendered

    // Create the filter object. For more information see https://go.microsoft.com/fwlink/?linkid=2153364

  
        const filter2 = {
            $schema: "http://powerbi.com/product/schema#basic",
            target: {
                table: "Alphabetical_list_of_products",
                column: "CategoryName"
            },
            operator: "In",
            values: ["Produce", "Seafood"]
        };

        // Add the filter to the report's filters.
    async function fn3() {
        setTimeout(() => {
            report.updateFilters(models.FiltersOperations.Add, [filter2])
        })
    }

    async function fn() {
        try {
            const res1 = await reportLoaded
            console.log('reportLoaded：' + res1)
            const res2 = await reportRendered
            console.log('reportRendered：' + res2)
            const res3 = await fn3()
            console.log('fn3()：' + res3)
        } catch (error) {
            console.log('异常：' + error)
        }
    }
    fn()
    

</script>

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI!

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

Trouble applying filters to Power BI report embedded with Excel dataset

Helpful resources

Microsoft Fabric & AI Learning Hackathon

Fabric Community Update - October 2024