Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to 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
)
)
Hi @AlB @Anonymous !
Both solutions works perfectly!
Thank you very much, it was my first time asking for help, and you made my day!
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. |
// 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
)
)
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!
[# 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.
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. |
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
22 | |
20 | |
15 | |
13 |
User | Count |
---|---|
49 | |
41 | |
39 | |
19 | |
19 |