Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
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 |
Hi @carted01 uz Power Query to do UNION query and then remove duplicate keys, after that use measure to countrows (before this backup your work :)). I hope this help.
Proud to be a Super User!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
51 | |
27 | |
24 | |
13 | |
9 |
User | Count |
---|---|
74 | |
56 | |
47 | |
16 | |
12 |