Compare values from two dynamically selected columns

[alinamarinbadea]

Hello! I am stuck with the following task:

I have a much bigger version of this sample table:

Attribute	Product 1	Product 2	Product 3	Product 4
Length	13	12	45	32
Width	23	32	11	2
Height	15	5	12	11
UoM Dimension	mm	mm	mm	mm
Net Weight	25	24	25	21
Gross Weight	41	25	41	50
UoM Weight	g	g	g	g

The task is to compare determine difference between the values for two dynamically selected products. 

Steps I have taken: 

1. I have duplicated my table

2. Created a relationship 1 to 1 between the two tables using the Attribute column

3. I have created 2 parameters 

Select Product A = {

    ("Product 1", NAMEOF('Table'[Product 1]), 0),

    ("Product 2", NAMEOF('Table'[Product 2]), 1),

    ("Product 3", NAMEOF('Table'[Product 3]), 2),

    ("Product 4", NAMEOF('Table'[Product 4]), 3)

} 

And 

Select Product B = {

    ("Product 1", NAMEOF('Table 2'[Product 1]), 0),

    ("Product 2", NAMEOF('Table 2'[Product 2]), 1),

    ("Product 3", NAMEOF('Table 2'[Product 3]), 2),

    ("Product 4", NAMEOF('Table 2'[Product 4]), 3)

}

4. Created a table visual where I have added Attribute, Select Product A and Select Product B and the result is the desired one. 

Now, I would like to create a measure or a calculated column that would calculate the difference in percentages between the values on each row for the products selected in the slicers (single select). If the value is not a number, "-" must be displayed

Appreciate any help I could get.

Thank you!

[Anonymous · Accepted solution]

Hi, @alinamarinbadea 

Thanks for amitchandak's reply. You can try the way from the link, or try to change the data model and then use the following dax.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8knNSy/JUNJRMjQGEUZAwsQUSBgbKcXqRCuFZ6aAZY2MIWJAJYYgLljSIzUzPaMEJAbSYQrTD1QBkg3N91VwycxNzSvOzM8DCufmohMgVX6pJQrhMHOMQGYYmcBZEIPci/KLixGKTAxh8mCWqQHcNriSdBQcGwsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Attribute = _t, #"Product 1" = _t, #"Product 2" = _t, #"Product 3" = _t, #"Product 4" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Attribute", type text}, {"Product 1", type text}, {"Product 2", type text}, {"Product 3", type text}, {"Product 4", type text}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Attribute"}, "Attribute.1", "Value"),
    #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Columns",{{"Attribute.1", "Product"}}),
    #"Filtered Rows" = Table.SelectRows(#"Renamed Columns", each ([Value] <> "g" and [Value] <> "mm")),
    #"Changed Type1" = Table.TransformColumnTypes(#"Filtered Rows",{{"Value", Int64.Type}})
in
    #"Changed Type1"

First Product = 
VAR _min =
    CALCULATE (
        SELECTEDVALUE ( 'Product'[Value] ),
        FILTER ( 'Product', 'Product'[Product] = MIN ( 'Product'[Product] ) )
    )
RETURN
    _min

Second Product =
VAR _max =
    CALCULATE (
        SELECTEDVALUE ( 'Product'[Value] ),
        FILTER ( 'Product', 'Product'[Product] = MAX ( 'Product'[Product] ) )
    )
RETURN
    _max

diff % = 
VAR _firstProduct =
    CALCULATE (
        SELECTEDVALUE ( 'Product'[Value] ),
        FILTER ( 'Product', 'Product'[Product] = MIN ( 'Product'[Product] ) )
    )
VAR _secondProduct =
    CALCULATE (
        SELECTEDVALUE ( 'Product'[Value] ),
        FILTER ( 'Product', 'Product'[Product] = MAX ( 'Product'[Product] ) )
    )
RETURN
    DIVIDE ( _secondProduct, _firstProduct )

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[amitchandak]

@alinamarinbadea , refer to this example. You need one of the table as an independent table

Compare Categorical Data Using Slicers - Compare two Brands: https://youtu.be/exN4nTewgbc