Comparing attributes of 2 products

[yzc]

Dear friends, 

I have a table which describes the attributes of all products.

Here is sample data: 

Here we have 2 products P1 & P2. They have 2 attributes A & B. 

Product	Duplicate of Product	Attribute	Value
P1	P1	A	V1
P1	P1	B	V2
P2	P2	A	V3
P2	P2	B	V2

My target is to calculate the difference between the selected 2 products. The 2 selections are determined by report reader via 2 slicers. 

e.g. as you can see, If I have chosen P1 and P2, Then the result should be: 

Product	Attribute	Value
P1	A	V1
P2	A	V3

Which tells me that P1 and P2 have differences in Attribute A. 

My DAX code is as follow: 

Result = 

VAR ProductA = SELECTEDVALUE('ProductTable'[Product])  -- to get The first choice

VAR ProductB = SELECTEDVALUE('ProductTable'[Duplicate of Product]) -- to get the second choice

VAR TableA = CALCULATETABLE(ProductTable, 'ProductTable'[Product] = ProductA)

VAR TableB = CALCULATETABLE(ProductTable, 'ProductTable'[Product] = ProductB)

VAR DiffResullt = EXCEPT(TableA, TableB)

RETURN DiffResult

But in fact it returned a blank table. 

Could you please help me with this, how to get this done? 

Great thanks in advance!

[Mahesh0016]

Hello @yzc,

Upon analyzing the issue, it appears that the problem lies within the `EXCEPT(TableA, TableB)` function. The `EXCEPT` function retrieves unique values from the first table that do not appear in the second table. Consequently, if there are no unique values between the two tables, the result will be blank.

[yzc]

I think The CALCULATETABLE(ProductTable, 'ProductTable'[Product] = ProductA) returns the filterred result of every columns in ProductTable, in SQL it is like

SELECT * FROM ProductTable WHERE ProductTable.Product = ProductA

But in fact I only need some of the fields, especially I don't need Product. SQL is like: 

SELECT Attribute, Value FROM ProductTable WHERE ProductTable.Product = ProductA. 

But how could I express in DAX? Great Thanks

[yzc]

Thanks for your reply, yes, I missed this......😂

But how can I get the result? How should I modify my code? Thanks in advance👍

[Mahesh0016]

Hello @yzc,

To obtain the desired result, you need to select values from only one slicer, not both. This is because the same table isn't filtered across both slicers. For example, you can select one value (P1) from the "Product Slicer" and another value (P2) from the "Duplicate of Product" slicer. As a result, it won't yield any value. To address this, you should plot one slicer, either "Product Slicer" or "Duplicate of Product," to achieve your expected outcome.

[yzc]

Hi Mahesh, 

Thanks for your advice. But I need to compare the 2 products. One slicer means I can only have one selection. If I duplicate the ProductTable, and use the "Product" field for the second slicer, will it work? I tried before but it still retured a blank table. Great thanks

[Mahesh0016 · Accepted solution]

@yzc ,
Result =

VAR TableA =
CALCULATETABLE (
                    SELECTCOLUMNS (
                                    FILTER ( Table1, Table1[Product] IN { "P1" } ),
                                    "Value", Table1[Value]
                                   )
               )
VAR TableB =
                   CALCULATETABLE (
                                    SELECTCOLUMNS (
                                                    FILTER ( Table1, Table1[Duplicate of Product] IN { "P2" } ),
                                                    Table1[Value]
                                                   )
                                   )
VAR TB =
               INTERSECT ( TableA, TableB )
VAR Diff_Resullt =
                  UNION (
                            EXCEPT ( TableA, TableB ),
                            CALCULATETABLE (
                                             SELECTCOLUMNS ( FILTER ( Table1, Table1[Value] <> TB ), Table1[Value] )
                                            )
                         )
RETURN
CALCULATETABLE (
                 FILTER (
                           Table1,
                                  Table1[Value] = FIRSTNONBLANK ( DISTINCT ( Diff_Resullt ), 0 )
                                  || 
                                  Table1[Value] = LASTNONBLANK ( DISTINCT ( Diff_Resullt ), 0 )
                       )
               )