cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

Comparing attributes of 2 products

Dear friends,

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.

1 ACCEPTED SOLUTION
Super User
`@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] )                                            )                         )RETURNCALCULATETABLE (                 FILTER (                           Table1,                                  Table1[Value] = FIRSTNONBLANK ( DISTINCT ( Diff_Resullt ), 0 )                                  ||                                   Table1[Value] = LASTNONBLANK ( DISTINCT ( Diff_Resullt ), 0 )                       )               )`
6 REPLIES 6
Super User

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.

Frequent Visitor

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

Frequent Visitor

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

Super User

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.

Frequent Visitor

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

Super User
`@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] )                                            )                         )RETURNCALCULATETABLE (                 FILTER (                           Table1,                                  Table1[Value] = FIRSTNONBLANK ( DISTINCT ( Diff_Resullt ), 0 )                                  ||                                   Table1[Value] = LASTNONBLANK ( DISTINCT ( Diff_Resullt ), 0 )                       )               )`

Announcements

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors