cancel
Showing results for
Did you mean:
Frequent Visitor

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

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?

1 ACCEPTED SOLUTION
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
)
)``````

6 REPLIES 6
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!

Super User III

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.

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

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!

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.

Super User III

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.

Announcements