Forum Discussion
need two slicers
You need to split your data. You can do this in Power Query.
- You have factproject table.
- Create a reference to that table which creates a new table.
- merge it with the DIMNAME table using the PK_DimEmployee_SalesRep field and expand the table.
- Select the columns you want to keep (probably salesrep and employeename) and remove other columns.
- Create another reference to factproject table.
- merge it with DIMNAME but this time use the PK_DimEmployee_SalesEngineer field.
- Select columns to keep, then remove other columns.
- Load both tables.
- 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.
You need to split your data. You can do this in Power Query.
- You have factproject table.
- Create a reference to that table which creates a new table.
- merge it with the DIMNAME table using the PK_DimEmployee_SalesRep field and expand the table.
- Select the columns you want to keep (probably salesrep and employeename) and remove other columns.
- Create another reference to factproject table.
- merge it with DIMNAME but this time use the PK_DimEmployee_SalesEngineer field.
- Select columns to keep, then remove other columns.
- Load both tables.
- 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.
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.
Hi Anonymous ,
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
