Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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!
User | Count |
---|---|
70 | |
46 | |
21 | |
21 | |
15 |
User | Count |
---|---|
124 | |
42 | |
39 | |
28 | |
24 |