help me with right DAX script / any other way to visualize in PowerBI reports

[raghuinpowerbi]

In my powerbi reports, i would like to visualize the table of similar products based on the selection of one particular product.

Similarity decisions should be taken based on the columns/criteria chosen.

Say example, below is the products table.

CriteriaFields table is the list of column names of Product table, usually.

Visual report, it is like below:

If i choose product C and in criteria fields Color, it should list other similar products which is same color as of Product C.

if i choose product C and in criteria fields Color & Size, it should list other similar products which is of same color and size as of Product C.

Can you please suggest me a better way to do it ?

[raghuinpowerbi]

This DAX is not generalised and is not working for any number of criteria items say, if the Criteria[Criteria] has more than 2 criterias and if multiple selection happens.

Say, if Criteria[Criteria] has 5 items called "color, size, manufactured by, marketed by, cost"

using the same dax script given, if we chose criteria as color, size, cost , how can i adapt the script for this kind ?

[raghuinpowerbi]

thats quick and perfect. thank you @Jihwan_Kim 

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure how your datamodel looks like, but I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file if it suits your requirement.

Color measure: = 
IF (
    HASONEVALUE ( Data[Product] ),
    VAR _productselectlist =
        VALUES ( 'Product'[Product] )
    VAR _colorselectlist =
        SUMMARIZE (
            FILTER ( ALL ( Data ), Data[Product] IN _productselectlist ),
            Data[Color]
        )
    VAR _sizeselectlist =
        SUMMARIZE (
            FILTER ( ALL ( Data ), Data[Product] IN _productselectlist ),
            Data[Size]
        )
    RETURN
        SWITCH (
            TRUE (),
            SELECTEDVALUE ( Criteria[Criteria] ) = "Color", MAXX ( FILTER ( Data, Data[Color] IN _colorselectlist ), Data[Color] ),
            SELECTEDVALUE ( Criteria[Criteria] ) = "Size", MAXX ( FILTER ( Data, Data[Size] IN _sizeselectlist ), Data[Color] ),
            MAXX (
                FILTER (
                    Data,
                    Data[Color]
                        IN _colorselectlist
                            && Data[Size] IN _sizeselectlist
                ),
                Data[Color]
            )
        )
)

Size measure: = 
IF (
    HASONEVALUE ( Data[Product] ),
    VAR _productselectlist =
        VALUES ( 'Product'[Product] )
    VAR _colorselectlist =
        SUMMARIZE (
            FILTER ( ALL ( Data ), Data[Product] IN _productselectlist ),
            Data[Color]
        )
    VAR _sizeselectlist =
        SUMMARIZE (
            FILTER ( ALL ( Data ), Data[Product] IN _productselectlist ),
            Data[Size]
        )
    RETURN
        SWITCH (
            TRUE (),
            SELECTEDVALUE ( Criteria[Criteria] ) = "Color", MAXX ( FILTER ( Data, Data[Color] IN _colorselectlist ), Data[Size] ),
            SELECTEDVALUE ( Criteria[Criteria] ) = "Size", MAXX ( FILTER ( Data, Data[Size] IN _sizeselectlist ), Data[Size] ),
            MAXX (
                FILTER (
                    Data,
                    Data[Color]
                        IN _colorselectlist
                            && Data[Size] IN _sizeselectlist
                ),
                Data[Size]
            )
        )
)