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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
newgirl
Helper V
Helper V

Unique Values from Multiple Fact Tables to be Used as a Slicer

Hi! 

 

I hope you can help me.

 

In my file, I have 3 fact tables.

 

Fact Table 1

Customer CodeRevenueDate
1231000Jan-20
2346000Mar-20
1233000Apr-20
3454000Jan-20
6787000

Feb-20

 

Fact Table 2

Customer CodeVolumeDate
12340Mar-20
23420Jul-20
12330Aug-20
34510Jun-20

 

Fact Table 3

Customer CodeCostDate
1238000Aug-20
2344000Jun-20
1233000Apr-20
3452000Jan-20

 

Then I have a Dimension Table that lists down all customer codes and the salesperson tagged to the customer. It's possible in this table that some clients do not buy from us anymore and therefore:

- the Salesperson tagged to it was not updated anymore (as represented by Old1) and that 

- it's possible that the customer code appears in this table but has no transactions in the fact tables

Customer CodeSalesPerson
123New1
234New2
345New3
456Old1

 

 

So anyway, in my report, I have to show in one page the reported values from the 3 fact tables and add a slicer of salesperson for the reader to choose. If I simply connect a relationship between the dimension table and the fact table, and then put the DimensionTable[Salesperson] field in the slicer, it will list down even the old salespersons, which I do not want the reader to see the old ones.

 

So in Power Query, I merged the queries between Dimension table and fact tables so I had this result:

Customer CodeRevenueDateSalesPerson
1231000Jan-20New1
2346000Mar-20New2
1233000Apr-20New1
3454000Jan-20New3
6787000Feb-200

 

 

Customer CodeVolumeDateSalesPerson
12340Mar-20New1
23420Jul-20New2
12330Aug-20New1
34510Jun-20New3

 

Customer CodeCostDateSalesPerson
1238000Aug-20New1
2344000Jun-20New2
1233000Apr-20New1
3452000Jan-20New3

 

 

For the slicer, I then created a new table using DAX with this code:

 

Salesperson_List = distinct(union(values(Fact1[Salesperson]),values(Fact2[Salespeson]),values(Fact3[Salesperson])))

 

so now I have a list of the salespersons that showed up in the fact tables, including the 0, which I also need, because I want is if the reader filters the 0 value in the Salesperson filter, they would see which transactions (or rows) are reading empty Salesperson.

 

So after I made this separate table above, I added a slicer in the page but it looks like the slicer doesn't filter the reports.

I tried to create a relationship between the Salesperson_List and the fact tables, but a notice pops up saying "circular dependency was detected...." 

 

I then tried creating a relationship between the SalesPerson_List and the Dimension Table but it's weird because it says it detects many-to-many relationships even though the Salesperson_List  has a DISTINCT formula in it.

 

My questions are...

 

A. How do I get the SalesPerson_List to work in filtering the reports? or

 

B. If that's not the direction I should be taking, what's the other way for me to have a slicer in the report that shows only the salespersons with transactions from the fact tables but I also need the 0 salesperson (meaning the transaction has no salesperson looked up from the dimension table) 

2 REPLIES 2
amitchandak
Super User
Super User

@newgirl ,

see if this can work

Salesperson_List = distinct(union(all(Fact1[Salesperson]),all(Fact2[Salespeson]),all(Fact3[Salesperson])))

 

Also check if merge and append can help

https://radacad.com/append-vs-merge-in-power-bi-and-power-query

and then delete duplicates -https://backtosql.wordpress.com/2019/04/19/the-easy-way-to-duplicate-tables-in-power-bi/

Greg_Deckler
Super User
Super User

@newgirl - Try getting rid of VALUES and use DISTINCT instead. Or, create your list of sales people in Power Query by right-clicking the original queries and choosing Reference. Remove all of the columns except the one you want. Repeat on the other fact table queries. Use an Append query to append them all together. Remove duplicates. Right click three intermediate queries and choose to not load them into the model.


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.