need two slicers

[kan]

Hi ,

       I have a Dimension table  dimname

PK_DimEmployee	FullName
-1	Unknown
1	Andy Gattorna
2	Andy Robertson
3	Ben Jones
4	Bill Delgrego
5	Bill DelGrego
6	Brent Wade
7	Brian Gough

in fact table factproject

PK_DimEmployee_SalesRep	PK_DimEmployee_SalesEngineer
36	2
7	-1
7	3
6	4
5	4
5	-1
5	-1
6	-1

in power bi report I need two slicers SalesRep and SalesEngineer.I cannot directly use fact table as slicer will show only numbers.I can use dimension table but it will give me one slicer with all values which I dont want as it needs to be two different slicers.I can write a query by using join condition and pull data from both tables and get into two different slicers.But I just want to know if there is any other possibility in power bi without writing  query in database.with any dax or calculated column??

Can someone please suggest.

Thanks..

[edhans · Accepted solution]

You need to split your data. You can do this in Power Query. 

1. You have factproject table.
2. Create a reference to that table which creates a new table.
3. merge it with the DIMNAME table using the PK_DimEmployee_SalesRep field and expand the table. 
4. Select the columns you want to keep (probably salesrep and employeename) and remove other columns.
5. Create another reference to factproject table.
6. merge it with DIMNAME but this time use the PK_DimEmployee_SalesEngineer field.
7. Select columns to keep, then remove other columns.
8. Load both tables.
9. use these as DIM tables in your model in DAX. Depending on how you want these to behave when slicing and cross-filtering, you might or might not need to enable bi-directional filtering on the relationships.

At this point, you may or may not want to UNLOAD the original FACTPROJECT table. It might be redundant in DAX at this point. It just becomes a subquery at that point.

[kan]

Thank you for the solution.

Just want to add step 10 where need to remove duplicates from new dimtables so as to establish relationships without errors

Also i would like to ask 1) if this works for direct query mode instead of import option.

2)what if new employees are added to the list of factproject and dimname.Will this still work or do i need to repeat the same process.

Thanks.

[v-yulgu-msft]

Hi @kan ,

1) With composite models, merging queries is supported in Direct Query mode.

2) If the data source is updated, you just need to click the refresh button in desktop to reflact the changes on Power BI side.

Regards,

Yuliana Gu