Skip to main content
cancel
Showing results for 
Search instead 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

Reply
yzc
Frequent Visitor

Comparing attributes of 2 products

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. 

ProductDuplicate of ProductAttributeValue
P1P1AV1
P1P1BV2
P2P2AV3
P2P2BV2

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: 

ProductAttributeValue
P1AV1
P2AV3

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!

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

View solution in original post

6 REPLIES 6
Mahesh0016
Super User
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.

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

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

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

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.

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

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

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

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 Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

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.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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