cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
gustalem
Frequent Visitor

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

Hi all, 

 

I have this tables:

gustalem_0-1620966714094.png

 

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?

1 ACCEPTED SOLUTION
daxer
Solution Sage
Solution Sage

 

 

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

 

daxer_0-1621019233430.png

 

View solution in original post

6 REPLIES 6
gustalem
Frequent Visitor

Hi @AlB @daxer !

Both solutions works perfectly!

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

AlB
Super User III
Super User III

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

 

 

SU18_powerbi_badge

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.

 

 

daxer
Solution Sage
Solution Sage

 

 

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

 

daxer_0-1621019233430.png

 

View solution in original post

gustalem
Frequent Visitor

Hi @daxer & @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!

daxer
Solution Sage
Solution Sage

[# 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
Super User III
Super User III

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

 

 

SU18_powerbi_badge

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.

   

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

Top Solution Authors
Top Kudoed Authors