Guitarmageddon
Board Regular
- Joined
- Dec 22, 2014
- Messages
- 161
Hey folks. I used to use this all the time and its fallen out of memory since a lot of this is a perishable skill....
I have many vendors, across a list of buyers. Some vendors belong to just one of our buyers, sometimes the buyers share a vendor.
I have a "count distinct" to show how many distinct buyers are buying one vendor. When I expand the table out to show the buyers however, the count distinct then shows all "1" because I dont have the filter context right in the formula. For example....
This formula:
=DISTINCTCOUNT([Buyer])
Gives me this when I have all my fields collapsed,
But when I expand out the vendor column to show the constituent buyers, those distinct counts all now show "1".
How did I need to change the formula to ALWAYS remain showing that distinct count of buyers (within each vendor name/number, whichever you decide)? I vaguely recall some mix of ALL, CALCULATE, FILTER....and so forth but its all a fog to me now, been a while since Ive seriously commited myself to dax, and it shows.
I have many vendors, across a list of buyers. Some vendors belong to just one of our buyers, sometimes the buyers share a vendor.
I have a "count distinct" to show how many distinct buyers are buying one vendor. When I expand the table out to show the buyers however, the count distinct then shows all "1" because I dont have the filter context right in the formula. For example....
This formula:
=DISTINCTCOUNT([Buyer])
Gives me this when I have all my fields collapsed,
Vendor # | Vendor Name | BUYER | distinct count buyer |
1234 | VENDOR A | 3 | |
7654 | VENDOR C | 2 | |
9999 | VENDOR B | 1 | |
34567 | VENDOR D | 3 | |
Grand Total | 9 |
But when I expand out the vendor column to show the constituent buyers, those distinct counts all now show "1".
Vendor # | Vendor Name | BUYER | distinct count buyer |
1234 | VENDOR A | JONES | 1 |
1234 | VENDOR A | SMITH | 1 |
1234 | VENDOR A | DOE | 1 |
7654 | VENDOR C | HAMPTON | 1 |
7654 | VENDOR C | LINCOLN | 1 |
9999 | VENDOR B | DOE | 1 |
34567 | VENDOR D | OCONNOR | 1 |
34567 | VENDOR D | WASHINGTON | 1 |
34567 | VENDOR D | WILLIAMS | 1 |
Grand Total | 9 |
How did I need to change the formula to ALWAYS remain showing that distinct count of buyers (within each vendor name/number, whichever you decide)? I vaguely recall some mix of ALL, CALCULATE, FILTER....and so forth but its all a fog to me now, been a while since Ive seriously commited myself to dax, and it shows.