Multiple Y-axis from Cube data

[ofrede]

Hi,

I have a cube to which I connect. It has a rather simple fact with some meassures:

Prices:

Date

Value

The Index is then connected to my Commodities dimension:

Commodities:

Name

So imagine my Prices measures contains prices for different Commodities for certain dates and combined with the Commodities dimension, I can get the following information:

Date	Commodity	Value
2000-01-01	Ship	1000000
2000-01-01	Car	5000
2000-01-01	Banana	0.5

So now I have a fine index of different commodities.

But if I want to get this data into a graph, and I want to show Ships and Bananas on the same graph, my Bananas "disappears" (or rather: is very close to the 0-value on the Y-axis), compared to Ships.

What I would like to do is to put the Prices.Value where the commodity = Ship into the graphs "Values" field and Prices.Value where the commodity = Banana into the graphs "Secondary values" field.

As mentioned in the headline, I get my data from a cube, so (I guess) it is limited how many DAX-calculations I can make in PowerBI.

Is it possible to do something like the above? Or can it only be done by restructuring the cube?

[Anonymous · Accepted solution]

Hi @ofrede ,

You may try to add a measure to "seperate" the smallest and the biggest value like this:

Secondary = 
var _sum=SUM('Table'[Value])
return IF(_sum<2,_sum,BLANK())

Or enable the Zoom slicer options:

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ofrede]

Solution accepted!

And it gave me the hint for an even better solution (in my case)!
I have my data in groups (Fruits, Small vehicles, Large vehicles) which I can then filter on instead of the value.

So based on my real-life example, I have created a few new measures like this one:

Fruit =CALCULATE(sum(Commodities[Value]),CommoditySpec[Group]="Fruit")