Count only unique rows with a single matching value

[macfuller]

What is a DAX formula to count only those rows that have just one corresponding value in a column?

We can pay vendors in 4 ways - EFT, and 3 types of checks. DISTINCTCOUNT() will give me all the vendors we paid by EFT if I filter by payment method but it doesn't tell me vendors who *only* paid by EFT. I suspect it's some combination of the VALUES() statement, or perhaps I get all the vendors and then subtract those where SELECTEDVALUE() <> "EFT" but I'm hoping there's an elegant construction I missed.

I'm sure we'll next want to be able to list those vendors, so if there's a way to return the VALUES for the vendors that are only paid one way I'd love that too!

 

[macfuller]

I'm trying something along the lines of this but I get an error that it doesn't understand the [Method Count] in the CALCULATE filter section. The core SUMMARIZECOLUMNS returns the count of payment types by vendor name just as I want but I'm not sure how to apply the filter to DISTINCTCOUNT only those vendors with one payment type.

Unique Vendor Count by Payment Type:=
CALCULATE (
    SUMX (
        SUMMARIZECOLUMNS (
            Vouchers[Vendor Name],
            "Method Count", DISTINCTCOUNT ( vouchers[Method] )
        ),
        [Method Count]
    ),
    [Method Count] = 1
)

 

[macfuller]

This appears to work when I create it in the data model:

Vendor Count Using Single Payment Type :=
SUMX (
    SUMMARIZECOLUMNS (
        Vouchers[Vendor Name],
        "Method Count", IF ( DISTINCTCOUNT ( Vouchers[Method] ) = 1, 1, 0 )
    ),
    [Method Count]
)

However, when I try to insert the measure into a pivot table I get this error...

"SummarizeColumns() and AddMissingItems() may not be used in this context"

This link indicates this may be an ongoing limitation. So I'm hoping someone must have cracked getting a unique count!

 

[Ozeroth]

Hi macfuller,

Here's an idea for a measure that to return a count of Vendors whose only Methods are within the current selection, i.e. vendors with no Methods outside the current selection:

Count of Vendors whose only payment methods are within current selection =
VAR NonSelectedMethods =
    EXCEPT ( ALL ( Vouchers[Method] ), ALLSELECTED ( Vouchers[Method] ) )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( Vouchers[Vendor Name] ),
        FILTER (
            VALUES ( Vouchers[Vendor Name] ),
            ISEMPTY (
                CALCULATETABLE (
                    VALUES ( Vouchers[Method] ),
                    ALLEXCEPT ( Vouchers, Vouchers[Vendor Name] ),
                    NonSelectedMethods
                )
            )
        )
    )

The important logic is within FILTER(...) where Vendors are filtered down to those with an "empty" list of Methods when the NonSelectedMethods filter is applied, as well as ignoring any filters except Vendor Name.

If you select a single Method, this measure will do what you described, i.e return the count of vendors whose only Method is the selected Method.

If multiple Methods are selected, this measure returns the count of Vendors with Methods only within that list, but that could be tweaked if you want different behaviour.

Regards,
Owen