Can MDX be used for this?

Matty

Well-known Member
Joined
Feb 17, 2007
Messages
3,707
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:

ViewMonthSales
A20190110
A20190215
A2019035
A20190420
B2019015
B20190210
B20190315
B2019045

<tbody>
</tbody>

Dimension table is like this:

MonthShort Month
20190101
20190202
20190303
20190404

<tbody>
</tbody>

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

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

Short MonthAB
01105
021510
03515
04205

<tbody>
</tbody>

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:

Short MonthMeasure
0110
0215
0315
0420

<tbody>
</tbody>

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
 

Matty

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

Update...

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

Short MonthAB
01100
02150
03015
04200

<tbody>
</tbody>

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

Short MonthMeasure
0110
0215
0315
0420

<tbody>
</tbody>

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

Short MonthMeasure
010
020
030
040

<tbody>
</tbody>

Anyone got any ideas?

Cheers,

Matty
 

Forum statistics

Threads
1,081,415
Messages
5,358,533
Members
400,502
Latest member
price83

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top