Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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
Solved! Go to 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
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
@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
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!
User | Count |
---|---|
126 | |
78 | |
77 | |
60 | |
52 |
User | Count |
---|---|
164 | |
84 | |
68 | |
68 | |
58 |