Distinctcount with filter based on current row value

[ejheflin]

I am stumped on a seemingly simple issue. I have a single table of transactions and I would like to add a calculated column for some quality control. I have ProductID column and a ProductName column. I want to flag any productids that have more than 1 unique ProductNames.

=CALCULATE(DISTINCTCOUNT([ProductName]),FILTER(Data,Data[ProductID]=[ProductID]))

Any idea where i'm going wrong, here? I am definitely open to suggestions. I realize that I can accomplish the same thing with a pivot table count but I am hoping to nest this in with a few other QC formulas to keep things tidy.

Thanks for any suggestions!

 

[Ozeroth]

You just need to add EARLIER around one of the Data[ProductID] references.
Within FILTER, EARLIER(Data[ProductID]) refers to the value in the outer row context of the original table, while Data[ProductID] refers to the value in the inner row context of the Data table being filtered.

=
CALCULATE(
    DISTINCTCOUNT(Data[ProductName]),
    FILTER(Data,Data[ProductID]=[B]EARLIER[/B](Data[ProductID]))
)

Another way of doing it:

=
CALCULATE(
    DISTINCTCOUNT(Data[ProductName]),
    ALLEXCEPT(Data, Data[ProductID])
)

 

[ejheflin]

You just need to add EARLIER around one of the Data[ProductID] references.
Within FILTER, EARLIER(Data[ProductID]) refers to the value in the outer row context of the original table, while Data[ProductID] refers to the value in the inner row context of the Data table being filtered.

=
CALCULATE(
    DISTINCTCOUNT(Data[ProductName]),
    FILTER(Data,Data[ProductID]=[B]EARLIER[/B](Data[ProductID]))
)

Another way of doing it:

=
CALCULATE(
    DISTINCTCOUNT(Data[ProductName]),
    ALLEXCEPT(Data, Data[ProductID])
)

Wow, you nailed it. Now i need to go read up on this so i can understand it
THANK YOU!