Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
julesdude
Post Partisan
Post Partisan

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

SizeUnit Of Measurement
150SM
225SF
334SF
456SM

 

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

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.

Thanks @Anonymous 

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

julesdude_0-1662037365824.png

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?

Anonymous
Not applicable

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!


amitchandak
Super User
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...

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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