cancel
Showing results for
Did you mean:
Helper V

## Best Way to Switch Measurements to Change Values in Report

Hello all,

I need help with the best approach for this problem.

I have a table in my data model which, highly simplified, looks like this:

 Name Size Unit Of Measurement 1 50 SM 2 25 SF 3 34 SF 4 56 SM

In my report I have a drop down slicer that allows the user to select SM (Square Meters) or SF (Square Feet).

There is then a table in the report which is essentially like the one above and uses it for it's columns - it just displays columns by name and the size.

What I need is for the size shown to be converted into whatever unit of measurement the user has selected in the dropdown slicer.

What is the best way to approach this? Would it be to add to the table in the data model two conditional columns in Power Query that separate Size into an SF and SM column, providing a conversion from one to the other where necessary? Or can this all be handled in DAX as a measure that can then change the Size column in the table to display the selected value in SM or SF (I was thinking maybe SWITCH could be used for this, but I'm not sure how).

I'd be thankful for any help on the best or easiest solution/approach.

4 REPLIES 4
Responsive Resident

Hi Jules,

There is no one right way, but instead you should ask which is the most efficient. How i would do it is :

Step 1: Create calculated table with your unit of measurement

``````Scale =
DATATABLE (
"Scale", INTEGER,
"Denominator", INTEGER,
{
{ "Square Feet", 10 },
{ "Square Meters", 0.9 }
}``````

Step 2 : Create a new measure for size

``````Size =
VAR RealSize =
SUM (Table1[Size])

VAR Denominator =
SELECTEDVALUE (
Scale[Denominator],
1
)
VAR Result = RealValue * Denominator

RETURN
Result``````

Step 3 : Put your measure into a matrix

Hope that helps.

Helper V

Thanks @Dax_Noob

I'm trying to apply this but get the error message:

Is it possible to create a table in Power Query assigned to the drop down selection names in one column of it and these values in the next column? What should the above DATATABLE look like?

Also in your second snip of DAX I think the RealValue variable - is that supposed to be RealSize?

Responsive Resident

Hi Jules,

For step 1 please replace  "Integer" with "String" as per the below

``````Scale =
DATATABLE (
"Scale", STRING,
"Denominator", INTEGER,
{
{ "Square Feet", 10 },
{ "Square Meters", 0.9 }
}``````

And yes for 2nd snip , Yes it was meant to be real size. Apologies for the typo. Hope that helps!

Super User

@julesdude , Two columns or two measures can do. You can use switch or can consider field parameters

https://amitchandak.medium.com/power-bi-field-parameters-a-quick-way-for-dynamic-visuals-fc4095ae9af...

Learn Power BI - Full Course with Dec-2022, with new DAX functions like Window, Index, Offset !!
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!
Formatted Profit and Loss Statement with empty lines

Announcements

#### 2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

#### Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

#### 2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.

Top Solution Authors
Top Kudoed Authors