Can MDX be used for this?

Matty

Well-known Member
Joined
Feb 17, 2007
Messages
3,717
Hi Team,

I have a Pivot Table report linked up to a data model held in the Power BI Service. OLAP is used for fast querying.

I now need to present some of the data in a slightly different way and I'm wondering if MDX (in Excel) might be the solution.

Below is small example to try to illustrate what I'm trying to do...

Fact table is like this:

[TABLE="class: tableizer-table"]
<tbody>[TR="class: tableizer-firstrow"]
[TH]View[/TH]
[TH]Month[/TH]
[TH]Sales[/TH]
[/TR]
[TR]
[TD]A[/TD]
[TD]201901[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]201902[/TD]
[TD]15[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]201903[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]201904[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]201901[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]201902[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]201903[/TD]
[TD]15[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]201904[/TD]
[TD]5[/TD]
[/TR]
</tbody>[/TABLE]

Dimension table is like this:

[TABLE="class: tableizer-table"]
<tbody>[TR="class: tableizer-firstrow"]
[TH]Month[/TH]
[TH]Short Month[/TH]
[/TR]
[TR]
[TD]201901[/TD]
[TD]01[/TD]
[/TR]
[TR]
[TD]201902[/TD]
[TD]02[/TD]
[/TR]
[TR]
[TD]201903[/TD]
[TD]03[/TD]
[/TR]
[TR]
[TD]201904[/TD]
[TD]04[/TD]
[/TR]
</tbody>[/TABLE]

There is a relationship in place between the 'Month' columns within the data model.

My Pivot Table (Excel) is laid out like this:

[TABLE="class: tableizer-table"]
<tbody>[TR="class: tableizer-firstrow"]
[TH]Short Month[/TH]
[TH]A[/TH]
[TH]B[/TH]
[/TR]
[TR]
[TD]01[/TD]
[TD]10[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]02[/TD]
[TD]15[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]03[/TD]
[TD]5[/TD]
[TD]15[/TD]
[/TR]
[TR]
[TD]04[/TD]
[TD]20[/TD]
[TD]5[/TD]
[/TR]
</tbody>[/TABLE]

I want to create a new measure to 'pick out' values from A or B depending on the Short Month. For example, I might want data from A for Short Months 01, 02 and 04, but from B for Short Month 03. So I want a measure that will produce the following:

[TABLE="class: tableizer-table"]
<tbody>[TR="class: tableizer-firstrow"]
[TH]Short Month[/TH]
[TH]Measure[/TH]
[/TR]
[TR]
[TD]01[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]02[/TD]
[TD]15[/TD]
[/TR]
[TR]
[TD]03[/TD]
[TD]15[/TD]
[/TR]
[TR]
[TD]04[/TD]
[TD]20[/TD]
[/TR]
</tbody>[/TABLE]

I've had a play with the MDX Calculate Measure option, using IIF and AND functions, as well trying CASE WHEN, but I can't quite get the measure to work with both the fields I need it to reference, i.e. 'Short Month' and 'View'.

Is this kind of thing achievable with MDX?

Cheers,

Matty
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
Hi,

Update...

I've managed to write a Calculated Measure that returns the following:

[TABLE="class: tableizer-table"]
<tbody>[TR="class: tableizer-firstrow"]
[TH]Short Month[/TH]
[TH]A[/TH]
[TH]B[/TH]
[/TR]
[TR]
[TD]01[/TD]
[TD]10[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]02[/TD]
[TD]15[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]03[/TD]
[TD]0[/TD]
[TD]15[/TD]
[/TR]
[TR]
[TD]04[/TD]
[TD]20[/TD]
[TD]0[/TD]
[/TR]
</tbody>[/TABLE]

Which gets me half way there. However, I don't want the 'View' (A, B) split in there - what I want is this:

[TABLE="class: tableizer-table"]
<tbody>[TR="class: tableizer-firstrow"]
[TH]Short Month[/TH]
[TH]Measure[/TH]
[/TR]
[TR]
[TD]01[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]02[/TD]
[TD]15[/TD]
[/TR]
[TR]
[TD]03[/TD]
[TD]15[/TD]
[/TR]
[TR]
[TD]04[/TD]
[TD]20[/TD]
[/TR]
</tbody>[/TABLE]

But when I take out the A, B context from the Pivot Table, it displays 0 rather than summing those columns:

[TABLE="class: tableizer-table"]
<tbody>[TR="class: tableizer-firstrow"]
[TH]Short Month[/TH]
[TH]Measure[/TH]
[/TR]
[TR]
[TD]01[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]02[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]03[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]04[/TD]
[TD]0[/TD]
[/TR]
</tbody>[/TABLE]

Anyone got any ideas?

Cheers,

Matty
 
Upvote 0

Forum statistics

Threads
1,223,886
Messages
6,175,196
Members
452,616
Latest member
intern444

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top