Results 1 to 2 of 2

Thread: Can MDX be used for this?
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Feb 2007
    Location
    England
    Posts
    3,704
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Can MDX be used for this?

    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:

    View Month Sales
    A 201901 10
    A 201902 15
    A 201903 5
    A 201904 20
    B 201901 5
    B 201902 10
    B 201903 15
    B 201904 5

    Dimension table is like this:

    Month Short Month
    201901 01
    201902 02
    201903 03
    201904 04

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

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

    Short Month A B
    01 10 5
    02 15 10
    03 5 15
    04 20 5

    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 Month Measure
    01 10
    02 15
    03 15
    04 20

    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

  2. #2
    Board Regular
    Join Date
    Feb 2007
    Location
    England
    Posts
    3,704
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Can MDX be used for this?

    Hi,

    Update...

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

    Short Month A B
    01 10 0
    02 15 0
    03 0 15
    04 20 0

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

    Short Month Measure
    01 10
    02 15
    03 15
    04 20

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

    Short Month Measure
    01 0
    02 0
    03 0
    04 0

    Anyone got any ideas?

    Cheers,

    Matty

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •