Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

switch between two y-axis columns

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 cha...
Need Help
dedelman_clng's avatar
dedelman_clng
Community Champion
7 years ago

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's avatar
Anonymous
Not applicable
7 years ago

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's avatar
    dedelman_clng
    Community Champion
    7 years ago

    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