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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
chrisbev
New Member

Measure controlled by Dimension

Hello all,

 

I have an OLAP cube with multiple values contained within one measure, controlled by a dimension. For example, I have a measure called VALUE which contains Revenue, Budget or Orders - the measure displayed depends on a particular value selected in another dimension called MEASURE_TYPE.

 

I can't see how this can be selected in a PowerBI chart.

 

Anyone else have a cube designed this way?

 

thanks

Chris

6 REPLIES 6
v-qiuyu-msft
Community Support
Community Support

Hi @chrisbev,

 

Please place the MEASURE_TYPE to a slicer visual, or put in the Visual Level Filter for the chart.

 

Best Regards,
QiuyunYu

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thanks for the response - I dont think this will work. Let me give you an example.

 

I have a measure called VALUE, within that single measure is held both ACTUAL VALUE and BUDGET VALUE, the selection of which is controlled by selecting a member in another dimension calledd MEASURE_TYPE.

 

If i want a single gauge displayed which shows ACTUAL versus BUDGET, then i can only select one of these two measures to be shown within the gauge?

 

 

Would also be interested to see if anyone else has a cube designed this way?

 

kind regards

 

I do this frequently. I have a table called MeasureBehavior. In it is a single column, MeasureType. There are no relationships between this and other tables.

 

MeasureType
Actual
Budget

 

Maybe you'd have more rows if you wanted more measure swapping. "Loss", "Projection" etc.

 

I don't know what your data model is like but let's imagine you have a table called BudgetTable and a table called ActualTable, both with Amount columns that you want to sum up. Further, I'm going to assume that if neither "Actual" nor "Budget" is selected, or if more than of them is selected, you want this measure to do nothing at all, return blank. If not, substitute whatever you want it to do for both of the BLANK()s below.

 

Value = IF(
	HASONEVALUE(MeasureBehavior[MeasureType]),
	SWITCH(
		FIRSTNONBLANK(MeasureBehavior[MeasureType], 1),
		"Actual", SUM(ActualTable[Amount]),
		"Budget", SUM(BudgetTable[Amount]),
		BLANK()
	),
	BLANK()
)

 

If you already have measures written for Actual Value and Budget Value, substitute references to them for the SUM parts above.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Thanks for the reply - in your example, how would you plot (within PowerBI) a guage that showed Actual versus Budget?

 

cheers

Chris

 

I thought you were asking for a gauge that you could control with a slicer, where it would switch between showing actual and budget. If you want to plot actual against budget on a gauge, why would you not just write a measure for actual and a measure for budget, then put them both on the gauge?





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Agreed - the issue is that we already have a cube with the measures developed the way they have been, we can use this method within Pyramid Analytics as it allows that granularity of selection but did wonder if

a) PowerBI could handle it and

b) Whether many other people constructed their measures this way

 

cheers

Chris

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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