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.
I have a hierarchy Category > Product. A product can be once, twice, etc. or zero time in a category.
Like that:
- Category 1
- Product 1
- Product 1
- Product 2
- Category 2
- Product 1
- Product 3
- Category 3
- Product 1
- Product 3
- Product 3
- ...
I want to know if a Product is in several Category. I wanted to distinctcount products by category then sum the result.
But it seems like I do not know how to use GROUPBY()
Do you know how to handle it?
Thank you
Thank you for all these answers!
How could I get the total number of multi-category products.
Because, when I display the product column and the DAX mesure, I can know if a product is multi-category or not.
But when I put it in a card visual, I only get 1.
Is it possible to sum the distinct count of product by category for each product in a measure?
Regards
// For each product, this measure returns
// the total number of categories the product
// belongs to (regardless of how many categories
// are visible). Bear in mind that the bridge table
// ProductCategory should be hidden and slicing
// cannot be done on it. It's only an auxiliary
// table that associates products with their
// categories.
[# Total Categories] =
var __oneProdVisible = HASONEVALUE( Product[ProductId] )
var __result =
CALCULATE(
SUMX(
ProductCategory,
// If the type of the field below is int
// then you don't have to multiply by 1,
// but if it's boolean, you have to.
ProductCategory[Unit] * 1
),
ALL( Category )
)
return
if( __oneProdVisible, __result )
// This tells you how many products in the current
// context are multi-cat products. If you want to
// see 0 instead of BLANK, you can add 0 to
// the COUNTROWS function.
[# Multi-Cat Products] =
COUNTROWS(
FILTER(
VALUES( Product[ProductId] ),
[# Total Categories] > 1
)
)
Best
D
If a product can be in several categories, then there must be a bridge table that associates a product to categories. Say that you've got this setup: Product [1:*] ProductCategory [*:1] Category. You say that each product is associated with every category but there's a field in ProductCategory (say its name is Present) that has 1 in it when the category applies to the product and 0 if not. You want to know for each product if it's in at least 2 categories. So this would be a calculated column.
Here's the formula:
[In At Least 2 Categories] = // calc column
sumx(
relatedtable( ProductCategory ),
ProductCategory[Present] * 1
) > 1
If you want a measure that will work only for individual products...
[In At Least 2 Categories] =
var __oneProdVisible = HASONEVALUE( Product[ProductId] )
var __result =
SUMX(
ProductCategory,
// If the type of the field below is int
// then you don't have to multiply by 1,
// but if it's boolean, you have to.
ProductCategory[Unit] * 1
) > 1
return
if( __oneProdVisible, __result )
Best
D
Hi @Anonymous
try a measure
Measure = CALCULATE(DISTINCTCOUNT(Table[Category]), ALLEXCEPT(Table, Table[Product]))
Thanks for this answer!
What I have found (because it is in DirectQuery and difficult to see every lines) is that each Product is present in each Category but with a unit of 0 if they are not present.
How could I add a filter on object[unit] > 0? (it does not work like that)
Thanks
@Anonymous
try
Measure = CALCULATE(DISTINCTCOUNT(Table[Category]), ALLEXCEPT(Table, Table[Product]), Table[Unit]>0)
or
Measure = CALCULATE(DISTINCTCOUNT(Table[Category]), FILTER(ALL(Table), Table[Product]=SELECTEDVALUE(Table[Product]) && Table[Unit]>0))
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 |
---|---|
42 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
43 | |
36 | |
33 | |
18 | |
18 |