DistinctCount Considering Two Sales Fact Tables

DAX DAX Calculations
1

Dear EDNA Community:

I would like to understand the best way to calculate a DistinctCount of both products and customers which are found in two separate fact tables. There are two sales fact tables due to an acquisition and the amount of columns available in each table.

The first Sales Fact Table is an SQL table and arriving at counts is no problem.

The second table is a historical file in excel that is less robust that the first Sales Fact Table but does have key fields to join on such as ship date, Cust_ID and Part_ID.

Each Sales Fact table joins separately to the dimension tables.

I will attach a PowerPoint showing the key columns and model relationships.
Disct CT - multi Fact Tables.pptx (51.2 KB)

There is bound to be overlap, especially with the customer side when determining the DistinctCount.

Any input on handling this question is sincerely appreciated.

Best regards,

WhiteWater

2

Hi @Whitewater100,

You can maybe create a virtual table that assembly certain columns of your two fact tables and then do a distinct count on this virtual table.

Without any example, it’s unfortunately difficult for me to be more specific…

Best regards,
JBocher

3

Thank you for replying. I was hoping knowing that two separate fact tables, both with Cust_ID & Product_ID would be enough to go on.

I think the closest solution will be to do a COUNTROWS on a Distinct type of Union. Something like this for counting the products:
COUNTROWS(DISTINCT(UNION(DISTINCT(FirstTable[PART_ID]), DISTINCT(SecondTable[PART_ID]))))

I should probably test this on two small tables as a way to verify. I’ll post if this works out.

Thanks again for replying.