Parameters slicer

[gavin007]

I have 6 sales related tables which have the same column called company, it is 3 digit code like 100 is subsidary 1, 200 is subsidery 2. 

I want to create a parameter slicer in page to acheive the following objective.

-When user select one company code, all the tables will get filtered in the company column by that company.

-if user select all in slicer, there are not filter to apply or filter will be selected all in the company column for all 6 tables.

I am not sure how to do this parameter in DAX or M. Can anyone help?

[amitchandak · Accepted solution]

@gavin007 , do you common company code table joining to all these tables.

If not have one like

Company Code = distinct(union(all(Table1[code]),all(TableB[code]),all(TableC[code]),all(TableD[code]),all(Tablee[code])))

and join it all 6 tables and use code from this table slicer

[Anonymous]

not enough information to be useful. Please give a general reply that can be adapted. how would this work if the parameter is a date, not a code?

[gavin007]

Basically, I want to make the dynamic parameter in power query to control the company to be available in power bi report.  The folllowing link gives me a glispe of hope. I will test it to see if it works.

https://docs.microsoft.com/en-us/power-bi/connect-data/desktop-dynamic-m-query-parameters

[gavin007]

@amitchandak the 6 tables are mix of master tables like customer, item and transactional table like actual sales and budget sales.

they are linked together but not through company code. e.g. customer master table link to sales tables through customer code. There is no such table called company code to link them together. 

I guess my question is how to filter those 6 tables when user select the company code in the slicer. At the moment, i can do it only in parameter in power query. 

[gavin007]

@amitchandak i have fixed the problem by using normal slicer base on company column. Your idea is not the same as what i have implemented, but I take the idea of building a related table set.

I was confused with the parameter and slicer in a connected tables. My original idea is to have paremeter filter in power query so that when user select it, it will filter the data. The only problem with is is end user are not supposed to change what view they are allowed to see based on dynamic dataset, they should be restrained by RLS based on the same data set.