Count only unique rows with a single matching value

macfuller

Active Member
Joined
Apr 30, 2014
Messages
299
Office Version
  1. 365
Platform
  1. Windows
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!
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

macfuller

Active Member
Joined
Apr 30, 2014
Messages
299
Office Version
  1. 365
Platform
  1. Windows
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.

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

macfuller

Active Member
Joined
Apr 30, 2014
Messages
299
Office Version
  1. 365
Platform
  1. Windows
This appears to work when I create it in the data model:
Code:
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

Active Member
Joined
Dec 14, 2013
Messages
264
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:

Code:
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
 

Forum statistics

Threads
1,148,108
Messages
5,744,873
Members
423,907
Latest member
zerocool88

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top