Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
kan
Helper I
Helper I

need two slicers

Hi ,

       I have a Dimension table  dimname

PK_DimEmployeeFullName
-1Unknown
1Andy Gattorna
2Andy Robertson
3Ben Jones
4Bill Delgrego
5Bill DelGrego
6Brent Wade
7Brian Gough
 
in fact table factproject
 
PK_DimEmployee_SalesRepPK_DimEmployee_SalesEngineer
362
7-1
73
64
54
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..

1 ACCEPTED SOLUTION
edhans
Super User
Super User

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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

3 REPLIES 3
edhans
Super User
Super User

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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

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 @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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.