CODEWITHSUNDEEP
powerbidax
ecrins
ecrins

Reputation: 3

Power BI / DAX show count of distinct values in column

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

Answers (1)

smpa01
smpa01

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
    )
)

Solution

Upvotes: 1

Related Questions