switch between two y-axis columns

[Anonymous]

Hello, so I have a line graph. on the y-axis is a set of data set by a spesific unit. I would like to be able to switch from the current unit of data to a diffrent unit of data, is there a way to change over without editing in the visualizations tab under " Values". Currently i have two columns that convert the data to one unit or the other .

End goal is to let the user choose what units they would like to see the data presented in , ex: inches or cm. 

All help is great! thanks. 

     -  Collin

[dedelman_clng]

You will need to create a slicer with the different options you want, create a measure off of that slicer indicating what value is picked, then a measure that will calculate your value in various units

Measure to indicate chosen value

MType = SELECTEDVALUE(Slicer[Field])

Measure for bar or line

Bar/Line Value = 
SWITCH (
    [MType],
    "Value1", [Measure Unit 1],
    "Value2", [Measure Unit 2],
...
)

Hope this helps

David 

[Anonymous]

@dedelman_clng

I am very confused on how to do this.  I tried creating measures but it will not alllow me to add my columns that hold the unit  conversions.  one column take the value and converts it if not already to inches and the other column converts to centimeters if not already.  Also i have never incoperated a slicer in to a spesific calculation before . Could you explain alittle what the "slicer [Field]" is  and the switch statement measure . thanks for the help!

   - Collin

[dedelman_clng]

Here is how I have done it previously:

I create a table "Unit Type" with one column "Type" and 2 values:

Volume

Energy

I then create a measure on that table

MType = SELECTEDVALUE('Unit Type'[Type])

'Unit Type'[Type] will populate your slicer.

On the fact table, I create a measure that switches between calculations based on the value selected in the slicer

Total Volume = 
SWITCH (
    [MType],
    "Energy", CALCULATE (
        SUM ( FG[Volume] ),
        UoM[Unit of Measure] = "MMBTU"
    ),
    "Volume", CALCULATE (
        SUM ( FG[Volume] ),
        UoM[Unit of Measure] = "MSCF"
    )
)

SWITCH() is a multi-part IF statement. See this link SWITCH() for more information.

If you're still having trouble, see this link to give the community all of the necessary information:

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

David

[Anonymous]

@dedelman_clng

I dont want  to bother much more, just one last question on your setup , what is FG[volume] in the sense that

[name]                is a measure

'Table'[name]      is a table column

what is

name[name]       ??

unit choice =

        SWITCH(

                [MType],

                "dBuA",  20 * LOG10('Table1'[RequirementLevel]) + 60) ,

                "mA"   ,  POWER(10, ( ('Table1'[RequirementLevel] - 60) / 20) )

)

this is my switch statement right now,

I Continue to get this error:

"A single value for column 'RequirementLevel' in table 'Table1' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result."

sorry to bother, very greatful for the help.

 - Collin

[dedelman_clng]

FG[Volume] is a column.  The '' are only required around a table name if is has non-alphanumeric characters in it

(e.g. 'F G'[Volume])

You will have to do some kind of aggregation in your measure around RequirementLevel.  SUM is the most basic and works just fine if you have only 1 row on your Table1 table for each value of your X-axis (the chart will introduce the required row context).

Hope this helps

David