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 ...
  • tamerj1's avatar
    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
            )
        )