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

[shivam]

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?

[ChenwuZhu_Gmail · Accepted solution]

Hi @shivam ,

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.

Measure:

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

Result:

Best regards.

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

[ChenwuZhu_Gmail · Accepted solution]

Hi @shivam ,

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.

Measure:

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

Result:

Best regards.

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

[shivam]

@SpartaBI 

[SpartaBI]

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

[bilalrana]

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.

[shivam]

Hi @bilalrana, thanks for the reply.

The second scenario you mentioned is my case.

[bilalrana · Accepted solution]

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.