Forum Discussion

Raph's avatar
Raph
Helper III
4 years ago
Solved

Slice on an element present in different fields

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

 

Need Help
  • tamerj1's avatar
    tamerj1
    4 years ago

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

2 Replies

  • tamerj1's avatar
    tamerj1
    Community Champion
    4 years ago

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