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
Anonymous
Not applicable

I have got Mass of some items in both kg & lbs, now I want to create a 'Mass UoM' field for filters

Mass in Kg and Lbs is present in two separate columns. The Mass UoM filter should give user freedem to choose between Kg and Lbs so that the item mass is shown accordingly in visuals.

 

 

What logic should I apply to get this new column?

2 ACCEPTED SOLUTIONS
ChenwuZhu_Gmail
Resolver I
Resolver I

Hi @Anonymous ,

 

I think you only need to store one column in int format in the fact table. Use a measure to show the result when user selelcte Kg or Lbs.

ChenwuZhu_Gmail_0-1660038531871.png

 

Measure:

Measure =
IF (
    SELECTEDVALUE ( Slicer[slicer] ) = "Kg",
    SUM ( 'Table'[Mass/kg] ) & "Kg",
    FORMAT ( SUM ( 'Table'[Mass/kg] ) * 2.20462262, "#.00Lbs" )
)

Result:

ChenwuZhu_Gmail_3-1660039226107.png

ChenwuZhu_Gmail_4-1660039238794.png

 

 

Best regards.

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Considering your scenario, you have 2 columns, one contains value in KG and other contains in LBS but you aren't able to create filter to see the respective value.

For this you need to go to the Home -> Transform Data then On the Power Query Editor click on Home -> Enter Data. Enter 2 rows, first should contain KG and the second should contain LBS or you can copy paste the following code into a new query, Home -> Advanced Editor. 

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8nZXitWJVvJxClaKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [UOM = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"UOM", type text}})
in
#"Changed Type"

Now click on Apply and Close.

Now create a Custom Measure

Mass = IF(SELECTEDVALUE(Table[UOM]) = "KG", DATA[KG_MASS_COLUMN],
                IF(SELECTEDVALUE(Table[UOM]) = "LBS",DATA[KG_MASS_COLUMN]))

Now Put the Table[UOM] into a Filter and see the magic.

View solution in original post

6 REPLIES 6
ChenwuZhu_Gmail
Resolver I
Resolver I

Hi @Anonymous ,

 

I think you only need to store one column in int format in the fact table. Use a measure to show the result when user selelcte Kg or Lbs.

ChenwuZhu_Gmail_0-1660038531871.png

 

Measure:

Measure =
IF (
    SELECTEDVALUE ( Slicer[slicer] ) = "Kg",
    SUM ( 'Table'[Mass/kg] ) & "Kg",
    FORMAT ( SUM ( 'Table'[Mass/kg] ) * 2.20462262, "#.00Lbs" )
)

Result:

ChenwuZhu_Gmail_3-1660039226107.png

ChenwuZhu_Gmail_4-1660039238794.png

 

 

Best regards.

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous
Not applicable

@Anonymous Hey, already night my time zone, 21:35 so ping me here tomorrow and I'll take a look

bilalrana
Resolver I
Resolver I

The requirment need to be refined. According to the current situation stated above. you have mass of items in a same column with Unit (i.e. kg or lbs). In this scenario you need to create a custom column to mention the unit of measure in it using DAX command. Then use the new column as a filter.

In a second scenario if you have separate columns of kg and lbs you should create a custom column and write DAX considering the both columns for unit of measure. 

The true solution can be guided if you share the snapshot of available data.

Anonymous
Not applicable

Hi @bilalrana, thanks for the reply.

The second scenario you mentioned is my case.

Considering your scenario, you have 2 columns, one contains value in KG and other contains in LBS but you aren't able to create filter to see the respective value.

For this you need to go to the Home -> Transform Data then On the Power Query Editor click on Home -> Enter Data. Enter 2 rows, first should contain KG and the second should contain LBS or you can copy paste the following code into a new query, Home -> Advanced Editor. 

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8nZXitWJVvJxClaKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [UOM = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"UOM", type text}})
in
#"Changed Type"

Now click on Apply and Close.

Now create a Custom Measure

Mass = IF(SELECTEDVALUE(Table[UOM]) = "KG", DATA[KG_MASS_COLUMN],
                IF(SELECTEDVALUE(Table[UOM]) = "LBS",DATA[KG_MASS_COLUMN]))

Now Put the Table[UOM] into a Filter and see the magic.

Helpful resources

Announcements
Europe Fabric Conference

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.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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