DAX: How to do a speciall DISTINCT COUNT on ID if it

[gustalem]

Hi all, 

I have this tables:

I am trying to count all the ID that had sell at least one product in each of the following categories:

-Sell a product of Brand A & it is rare

-Sell a product of Brand B

-Sell a product of Brand C

In this case, it would be only one person; Juan.

Any suggestions which function I should use?

[gustalem]

Hi @AlB @Anonymous !

Both solutions works perfectly!

Thank you very much, it was my first time asking for help, and you made my day!

[AlB]

@gustalem 

This doesn't need a relationship between the tables:

Measure =
VAR auxT_ =
    FILTER (
        Table2,
        ( Table2[Brand] = "A" && Table2[SKU] = "RARE" ) || Table2[Brand] IN { "B", "C" }
    )
RETURN
    COUNTROWS (
        FILTER (
            DISTINCT ( Table1[Seller] ),
            COUNTROWS (
                INTERSECT (
                    { "A", "B", "C" },
                    CALCULATETABLE (
                        DISTINCT ( Table2[Brand] ),
                        TREATAS ( CALCULATETABLE ( DISTINCT ( Table1[IDProduct] ) ), Table2[IDProduct] ), auxT_)
                )
            ) = 3
        )
    )

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.

[Anonymous · Accepted solution]

// Connect T2 to T1 on [ID Product]
// with one-way filtering from T2 to
// T1.

[# Cust] =
COUNTROWS(
    FILTER(
        DISTINCT( T1[Seller] ),
        var HasType1Products =
            CALCULATE(
                NOT ISEMPTY( T2 ),
                KEEPFILTERS(
                    T2[Brand] = "A"
                    && 
                    T2[SKU] = "Rare"
                ),
                CROSSFILTER(
                    T2[ProdID],
                    T1[ProdID],
                    BOTH
                )
            )
         var HasType2Products =
            CALCULATE(
                NOT ISEMPTY( T2 ),
                KEEPFILTERS(
                    T2[Brand] = "B"
                ),
                CROSSFILTER(
                    T2[ProdID],
                    T1[ProdID],
                    BOTH
                )
            )
         var HasType3Products =
            CALCULATE(
                NOT ISEMPTY( T2 ),
                KEEPFILTERS(
                    T2[Brand] = "C"
                ),
                CROSSFILTER(
                    T2[ProdID],
                    T1[ProdID],
                    BOTH
                )
            )
         var HasAll3Types = TRUE()
            && HasType1Products
            && HasType2Products
            && HasType3Products
         return
            HasAll3Types
    )
 )

[gustalem]

Hi @Anonymous & @AlB 

Both options doesn't work for me 😞 

Maybe my question was confusing, but I need to count a seller if they sold at least on product in EACH category:
-Sell a product of Brand A & it is rare

-Sell a product of Brand B

-Sell a product of Brand C

Your solutions works to count if they sold at least one of the list, no matters what they sold in the others categorys.

In my example:
Juan sold a product 1 (A - Rare), product 3 (B) and product 4 (C), so i want to count him.
Maria sold a product 1 (A - Rare), product 2 (A - Common) and product 3 (B). She didn't sell a product brand C, so I dont want to count her.
In the same way, Pedro sold Product 1 (A -Rare), product 2 (A - Common) and product 4 (C - Common). He didn't sell a product brand B, so I don'w want to count him either.

Thank you all for your help, I really apreciatte it 🙂

I keep looking for a solution!

[Anonymous]

[# Cust] =
var ProdOfInterest =
    SELECTCOLUMNS(
        FILTER(
            ALL( T2 ),
            (T2[Brand], T2[SKU]) IN
            UNION(
                {("A", "Rare")},
                CROSSJOIN(
                    {"B", "C"},
                    ALL( T2[SKU] )
                )
            )
        )
        "@ProdID",
            T2[ID Product]
    )
var Result =
    CALCULATE(
        DISTINCTCOUNT( T1[Seller] ),
        KEEPFILTERS(
            TREATAS(
                ProdOfInterest,
                T1[ID Product]
            )
        )
    )
return
    Result

This code respects all filters put on the T1 (Table 1) table.

[AlB]

Hi @gustalem

Place this measure in a card visual

Measure =
VAR auxT_ =
    CALCULATETABLE (
        DISTINCT ( Table2[Id product] ),
        FILTER (
            Table2,
            ( Table2[Brand] = "A" && Table2[Brand] = "RARE" ) || Table2[Brand] IN { "B", "C" }
        )
    )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( Table1[Seller] ),
        TREATAS ( auxT_, Table1[Id product] )
    )

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.