Forum Discussion
Count Distinct Rows of Dimension Table using multiple fact tables
Hi,
I need a measure to count the distinct number of active accounts in any given time period. This has been easy in the past where I have only had one table for sales data. However with this new model, there are 4 seperate sales tables. Each has a relationship to the "Debtors" table with a unique key for each sales table (eg [ACCNO] for Direct sales, [Customer ID (ALM)] for ALM).
In the below table, I have made a measure for each seperate sales table, with a distinct count of account numbers within each. EG:
Distributions (ALM) = distinctcount('Sales Data (ALM)'[Customer ID (ALM)]),
This works fine when only looking at one sales source, but i wish to count the unique number of accounts across all 4
Adding the 4 measures together as is doesn't work, as it creates double ups if an account has purchased from more than one source, and the below test measure has a simlar effect.
Test = COUNTROWS(
DISTINCT(
UNION(
VALUES( 'Sales Data (Direct)'[ACCNO]),
VALUES( 'Sales Data (ALM)'[Customer ID (ALM)] ),
VALUES( 'Sales Data (LMX)'[Cust. No] ),
VALUES( 'Sales Data (Paramount)'[Company])
)))
Can anyone help?
In the below the answer I am after is 38, however I just can't seem to get there.
| Debtor Table [ACCNO] | Distributions (Direct) | Distributions (ALM) | Distributions (LMX) | Distributions (Paramount) | Distributions | Test |
| 14 | 1 | 1 | 1 | |||
| 37 | 1 | 1 | 1 | |||
| 67 | 1 | 1 | 1 | |||
| 376 | 1 | 1 | 1 | |||
| 534 | 1 | 1 | 1 | |||
| 580 | 1 | 1 | 1 | |||
| 631 | 1 | 1 | 1 | 2 | ||
| 646 | 1 | 1 | 1 | 2 | ||
| 651 | 1 | 1 | 1 | |||
| 652 | 1 | 1 | 1 | |||
| 653 | 1 | 1 | 1 | |||
| 658 | 1 | 1 | 1 | |||
| 663 | 1 | 1 | 1 | 2 | ||
| 719 | 1 | 1 | 1 | |||
| 752 | 1 | 1 | 1 | |||
| 795 | 1 | 1 | 1 | |||
| 796 | 1 | 1 | 1 | |||
| 930 | 1 | 1 | 1 | |||
| 934 | 1 | 1 | 1 | |||
| 984 | 1 | 1 | 1 | |||
| 1025 | 1 | 1 | 1 | |||
| 1058 | 1 | 1 | 1 | |||
| 1227 | 1 | 1 | 1 | |||
| 1299 | 1 | 1 | 1 | |||
| 1658 | 1 | 1 | 1 | |||
| 1757 | 1 | 1 | 1 | |||
| 1823 | 1 | 1 | 1 | |||
| 1975 | 1 | 1 | 1 | |||
| 2186 | 1 | 1 | 1 | 2 | ||
| 2187 | 1 | 1 | 1 | |||
| 2236 | 1 | 1 | 1 | |||
| 2261 | 1 | 1 | 1 | |||
| 2704 | 1 | 1 | 1 | |||
| 2748 | 1 | 1 | 1 | |||
| 2871 | 1 | 1 | 1 | |||
| 3403 | 1 | 1 | 1 | |||
| 3429 | 1 | 1 | 1 | |||
| 3746 | 1 | 1 | 1 | |||
| 38 | 1 | 42 |
