Reputation: 3
I'd like to expand the solution on this page.
The original solution is given in Power query. But I'd like a DAX solution...
I've created a table from the fields Source and Preparer. If I want to see those sources where there is only one preparer what's is the best way to do it via DAX?
Like the Sources "MN", "RT" and "PO".
Source | Preparer |
---|---|
AB | June |
AB | Jane |
MN | June |
SS | Kim |
SS | Lee |
SS | Jim |
RT | Kim |
PO | June |
PO | June |
RT | Kim |
The outcome I'm looking for is like:
Source | Preparer | Number |
---|---|---|
MN | June | 1 |
RT | Kim | 1 |
PO | June | 1 |
DAX that comes close looks like:
TableResult =
FILTER (
SUMMARIZECOLUMNS (
'Table'[Source],
"Number", DISTINCTCOUNT ( 'Table'[Preparer] )
),
[Number] = 1
)
This gives the source and the number, but not the Preparer.... Any ideas?
Upvotes: 0
Views: 3067
Reputation: 4346
Please try this
newMeasure =
CALCULATE (
DISTINCTCOUNT ( 'Table 1'[Preparer] ),
FILTER (
GROUPBY ( 'Table 1', 'Table 1'[Source], 'Table 1'[Preparer] ),
CALCULATE (
DISTINCTCOUNT ( 'Table 1'[Preparer] ),
ALLEXCEPT ( 'Table 1', 'Table 1'[Source] )
) = 1
)
)
Upvotes: 1