Forum Discussion
switch between two y-axis columns
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
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
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.- CollinFG[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