`Power BI Embedded for WordPress`, sending username to PowerBI side for filtering data

[Anonymous]

We have a wordpress website and it uses `Power BI Embedded for WordPress` (https://wordpress.org/plugins/embed-power-bi/) for fetching powerBI report and showing it on the page.

we want to filter data for each user so we want to send username from wordpress to PowerBI side so we can see the user asking for report and filter data according to him.

I know about the filters we can apply but I'm unable to plug them into it.

https://github.com/Microsoft/PowerBI-JavaScript/wiki/Filters

[Neel16]

Hey, I am also looking for this. Did you get the solution?

[Anonymous]

Hey Neel, not quite in yet but here's a workaround I posted: https://community.powerbi.com/t5/Developer/Power-BI-Embedded-for-WordPress-sending-username-to-Power...

[Anonymous]

Hey! My org (Atlas Public Policy) maintains that plugin. To my knowledge (correct me if I'm wrong, @npnigro) embedding filters isn't supported in the current version. 

However, there's a pretty easy workaround. You can just attach the filter object to the URL. The following code will generate a link that will filter a dashboard embedded with the plugin. Replace the sections in Bold to fit your usecase. 

var relatedFilterObj = [{"$schema":"http://powerbi.com/product/schema#basic","target":{"table":"User","column":"Username"},"operator":"=","values":["<Current Users Username>"]}];
var pageURL = "http://example.com/path/to/dashboard"
var relatedURL = pageURL + "?filters=" + encodeURIComponent(JSON.stringify(relatedFilterObj));

You should be able to hook this up to the page load by modifying your functions.php

[airbi]

Hey, firstly I love this plugin... it has saved me a lot of time.

I’m trying to get your code to work on my site... I would like to pass the email address of the user to filter a report.

Do you have any examples you can share of what this code looks like once added to the functions.php file?

Many Thanks

Darren.

[Anonymous]

Hey Darren!

Here's a lightly modified version of how I do it on one of our sites. I took the url generated from the above code and used it to make a template string that I fill in with the item I want to filter. (Modified for your usecase)

I attach the function to one of the early page load hooks and make sure to check there's not already something there first, then just redirect to the prefiltered url.

add_action('template_redirect', 'append_query_string');
function append_query_string($vars) {
	if (!isset($_GET['filters'])) {
        $current_user = wp_get_current_user();
        $email = $current_user->user_email; //You'll need to figure out how to get the email address you're looking for, if it's not the current user
		wp_redirect(add_query_arg('filters','%5B%7B%22%24schema%22%3A%22http%3A%2F%2Fpowerbi.com%2Fproduct%2Fschema%23basic%22%2C%22target%22%3A%7B%22table%22%3A%22User%22%2C%22column%22%3A%22Email%22%7D%2C%22operator%22%3A%22%3D%22%2C%22values%22%3A%5B%22' . ucwords ($email) . '%22%5D%7D%5D', get_permalink()));
	}
}

Hope this helps! Give my regards to Hannah and the AirBI team!

[Zap5619]

Hello @Anonymous 

Thank you for sharing your advice for this topic. My organization is looking to implement this code into our Wordpress site. So, all we would need to do is paste the two code blocks you sent into our functions.php page and it should work? Or is there something else that we would need to do?

[Anonymous]

Hey Zap!

Depends onwhat specifically you're trying to do.

Overall, you're trying edit URL to filter your dashboard. You can filter whatever you want by editing the URL. Revisiting the first set of code, you could drop this in your browser console or use it inline as JS somewhere on your site.  I use this to edit pages I'm already on and control the filters from outside the dashboard, but it could be used just one time to generate a prefiltered URL as well.

var relatedFilterObj = [{"$schema":"http://powerbi.com/product/schema#basic","target":{"table":"<Table to filter on>","column":"<Column value is in>"},"operator":"=","values":["<value to filter>"]}]; //Fill in the <tagged> areas
var pageURL = "http://example.com/path/to/dashboard" //replace this with your site's url or use a js function to grab it
var relatedURL = pageURL + "?filters=" + encodeURIComponent(JSON.stringify(relatedFilterObj)); //relatedURL will now contain the relevant URL

The second code block is more drag and drop. If what're you're trying to do is filter a table called 'User' with the column 'Email' to the email address of the current wordpress user, it'll work perfectly! Otherwise, fill out the <tags> below with your relevant info:

add_action('template_redirect', 'append_query_string');
function append_query_string($vars) {
	if (!isset($_GET['filters'])) {
		wp_redirect(add_query_arg('filters','%5B%7B%22%24schema%22%3A%22http%3A%2F%2Fpowerbi.com%2Fproduct%2Fschema%23basic%22%2C%22target%22%3A%7B%22table%22%3A%22<TABLE_NAME_HERE>%22%2C%22column%22%3A%22<COLUMN_NAME_HERE>%22%7D%2C%22operator%22%3A%22%3D%22%2C%22values%22%3A%5B%22' . ucwords ("<VALUE_TO_FILTER_TO_HERE>") . '%22%5D%7D%5D', get_permalink()));
	}
}

This will automatically change the URL on pageload (~ish) to pre-filter your dashboard. You may want to play with it some to get it to do exactly what you want.

Good Luck!

[Neel16]

Hey! I am looking for a code that allows me to filter Power BI data based on requirements and to implement the same code on a WordPress site to access the filtered dashboard. My PowerBI URL works fine when I edit and add the filter, but when I add it to the WordPress PowerBI plugin, it doesn't work under 'Report Section' area.
Please suggest some ideas.Thank you in advance!

[Anonymous]

Hey Neel,

I'm not sure if I'm understanding your use case correctly, but the above code for filtering is meant to be added to either your functions.php on your WP site or in your header as a javascript script (depending on which bit of code you're using above), not in the plugin.

Hope this helps