Create a summarize table with a filter without using a numeric metric

[jessicarocha]

Hi, 

I need to cross information between two fact tables that are not directly connected (both have connections with dimension customer and product). 
One fact table represents the customers that ordered that this specific product was produced and the other fact table represents all customers that bought this product.

I would like to know the total about of customers that ordered this given product, without counting the same customer twice if he appears in both fact tables. 

Fact Customers that bought the product.

Fact Customers that ordered the product

All customers that order a product to be made, have a Customer Type SS.

A page in the report has a filter per product. In this case, Product A. 

In total, there are 7 customers that ordered Product A. Customer 0001 is repeated in both tables but should only be counted once.

I used the following metric:

test = 
var CustOrder = SUMMARIZE('Fact Order','Dim Customers'[Customer], 'Dim Product'[Product] )
var CustBought = SUMMARIZE('Fact Bought','Dim Customers'[Customer], 'Dim Product'[Product] )
var CustBoth = DISTINCT(UNION( CustBought, CustOrder))
return COUNTROWS(CustBoth)

In my original report, I am getting a total of 8. It is not being able to distinguish that customer 0001 is repeated and should only be counted once. 

Thus, I thought about doing a filter in the SUMMARIZE table of the fact table of customers that bought the product to only include "Customer Type <> SS" but I don't know what the right sintax of the formula is...

Can you help me with that?

I created a dummy file. Unfortunatelly, in there I get exactly what I need. However, in reality, I still have the error. My real model has the same structrure, it is just bigger. 

Dummy example:
https://drive.google.com/drive/folders/13uPvVHdZuj7IMUUfhVVC5616tVDJyshb?usp=sharing

Since you can't see the error in my dummy file, here is a picture of my real model and the problem I face it.

Thanks in advance for the help!

[Anonymous · Accepted solution]

Hi @jessicarocha ,

Can't reach the shared file (need to sign in).

You can try to take out each step in the formula separately to find out which step is wrong.

You may consider to use change the column name in table2 and use crossjoin() instead of union().

Best Regards,

Jay

[jessicarocha]

Hi @Anonymous, thanks for the tips, I will try them now. Sorry about the file, it was restricted by mistake but I changed to open now. You should be able to access it. 

Thanks a lot for the help!

[Greg_Deckler]

@jessicarocha Hard to follow. You may be able to use CALCULATETABLE? Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

[jessicarocha]

Hi @Greg_Deckler , 

thanks for the tips. I edited my question and I hope it is more clear now.