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

[newgirl]

Hi! 

I hope you can help me.

In my file, I have 3 fact tables.

Fact Table 1

Customer Code	Revenue	Date
123	1000	Jan-20
234	6000	Mar-20
123	3000	Apr-20
345	4000	Jan-20
678	7000	Feb-20

Fact Table 2

Customer Code	Volume	Date
123	40	Mar-20
234	20	Jul-20
123	30	Aug-20
345	10	Jun-20

Fact Table 3

Customer Code	Cost	Date
123	8000	Aug-20
234	4000	Jun-20
123	3000	Apr-20
345	2000	Jan-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 Code	SalesPerson
123	New1
234	New2
345	New3
456	Old1

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 Code	Revenue	Date	SalesPerson
123	1000	Jan-20	New1
234	6000	Mar-20	New2
123	3000	Apr-20	New1
345	4000	Jan-20	New3
678	7000	Feb-20	0

Customer Code	Volume	Date	SalesPerson
123	40	Mar-20	New1
234	20	Jul-20	New2
123	30	Aug-20	New1
345	10	Jun-20	New3

Customer Code	Cost	Date	SalesPerson
123	8000	Aug-20	New1
234	4000	Jun-20	New2
123	3000	Apr-20	New1
345	2000	Jan-20	New3

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) 

[amitchandak]

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

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