topic Re: help me with right DAX script / any other way to visualize in PowerBI reports in DAX Commands and Tips

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

raghuinpowerbi — Thu, 26 Oct 2023 22:16:02 GMT

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 ?

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

Jihwan_Kim — Fri, 27 Oct 2023 03:31:10 GMT

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

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

raghuinpowerbi — Fri, 27 Oct 2023 11:05:23 GMT

thats quick and perfect. thank you Jihwan_Kim

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

raghuinpowerbi — Mon, 27 Nov 2023 11:00:36 GMT

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 ?