Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello,
Let's say I have products with different possible colors.
I would like to have a pivot table with one color that can be present in different fileds. In the exemple below, I would like to have Green on the row of my pivot table which will sum the sales of Product2, Product4 and Product6.
How do you suggest to proceed?
Thank you
Raphaël
Solved! Go to Solution.
Hi @Raph
Please do the following
Create a filter table containing all colors
Colors =
SELECTCOLUMNS (
FILTER (
DISTINCT (
UNION (
VALUES ( Products[Color1] ),
VALUES ( Products[Color2] ),
VALUES ( Products[Color3] )
)
),
[Color1] <> BLANK ( )
),
"Color", [Color1]
)
Then your measure would be
Number of Products =
VAR SelectedColor = SELECTEDVALUE ( Colors[Color] )
RETURN
COUNTROWS (
FILTER (
Products,
Products[Color1] = SelectedColor
|| Products[Color2] = SelectedColor
|| Products[Color3] = SelectedColor
)
)
Thank you very much !
Hi @Raph
Please do the following
Create a filter table containing all colors
Colors =
SELECTCOLUMNS (
FILTER (
DISTINCT (
UNION (
VALUES ( Products[Color1] ),
VALUES ( Products[Color2] ),
VALUES ( Products[Color3] )
)
),
[Color1] <> BLANK ( )
),
"Color", [Color1]
)
Then your measure would be
Number of Products =
VAR SelectedColor = SELECTEDVALUE ( Colors[Color] )
RETURN
COUNTROWS (
FILTER (
Products,
Products[Color1] = SelectedColor
|| Products[Color2] = SelectedColor
|| Products[Color3] = SelectedColor
)
)
User | Count |
---|---|
141 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
123 | |
101 | |
71 | |
61 |