Custom filters in Power BI

[sachin17]

Hi Experts,

I have a requirement in powerbi to create a single report which takes filters on product dimension based on the country dimension value. For example if this is executed for US it should filter the brand dimension for x,y,z brands and if UK executes this report then it should filter on a b c brands .  Is it possible to create a custom filter which filters according to a logic condition or some coding in the background which reads a sql table or you can write a logic to determine these values? 

If this cannot be done i will have to end up creating the same report for each country which is not the most efficient approach.

Thanks in advance

Sach

[Anonymous]

@sachin17: Can you add a table to your data with the following information?

Country	Product
UK	A
UK	B
US	C
US	D

And so on?

Is it ok if users in other countries see the data from the US, for example? If So I would just add a slicer on the Country code of the table above.

If not, you can add row level security so that users in one country can only see the data filtered to that country. More here: https://docs.microsoft.com/en-us/power-bi/admin/service-admin-rls

[speedramps]

Hi Sac

Please conisder this solution and leave kudos

Create a 1:many  Cross Filter relationship beween product dimension and your fact table.
Create a 1:many  Cross Filter relationship beween country dimension and your fact table.

Add a product slicer.
Add a country slicer.

Test it.
US should filter the brand dimension for x,y,z brands
and UK executes this report then it should filter on a b c brands.

Assuming your fact table is set up that way !

This solution works due to the Cross Filter.
Which applies the product slicer filter to country dimension via the fact table and vice versa. 

[sachin17]

Thanks for the quick response.    Will this work when you have filter say for example 5 default brands for UK , 7 default brands for US but remember that there are more brands in the table column. How can you set this by default ? In our current model the Uk user will only see UK brands . We dont want user to select these brands and  ideally these brand values are read from a configuration table or attribute and based on the country executing should pick the brand values automatically . 

[Anonymous]

If you want it to automatically filter based on the user who is accessing it, I believe Row Level Security is the right way to do it.

[sachin17]

Unfortunately not.  This is only for this report and RLS impacts the whole data model i guess . We are just trying to make reports dynamic here and i dont think we use security to achieve this. 

[Anonymous]

You can scope RLS to just that single table, and only have it impact that table. Not an elegant solution though - would be curious if there is a better solution out there.