none
Can MDX be used for this? RRS feed

  • Question

  • Hi Team,

    I have not used MDX before and, although reasonably good with DAX, I am struggling with the syntax.

    My original request is here:

    https://www.mrexcel.com/forum/power-bi/1106911-can-mdx-used.html

    Can anyone off any advice, please?

    Thanks,

    Matty


    Friday, August 16, 2019 9:55 AM

All replies

  • test33

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Hi Will,

    Suspect the link may have put off potential helpers.  I wasn't able to post any images on here due to being a brand new member, hence the link to the other forum which describes what I want help with.

    Hope you or someone else can assist.

    Thanks,

    Matty

    Monday, August 19, 2019 9:02 AM
  • Hi Matty1977,

    Here is the mdx query for your reference.

    WITH MEMBER [Measures].[Short Month] AS 
    [Dim Month].[Short Month].currentmember.MEMBERVALUE
         MEMBER [Measures].[Measure] AS
    [Measures].[Sales]
         SET SET1 AS
     FILTER ([Dim Month].[Short Month].[Short Month]*[Dim View].[View].[View], 
       ([Dim Month].[Short Month].MEMBERVALUE="01" AND [Dim View].[View].MEMBERVALUE="A")
     OR([Dim Month].[Short Month].MEMBERVALUE="02" AND [Dim View].[View].MEMBERVALUE="A")
     OR([Dim Month].[Short Month].MEMBERVALUE="03" AND [Dim View].[View].MEMBERVALUE="B")
     OR([Dim Month].[Short Month].MEMBERVALUE="04" AND [Dim View].[View].MEMBERVALUE="A")
     )
    SELECT 
    {
    [Measures].[Short Month],
    [Measures].[Measure]
    } ON 0,
    SET1 ON 1
    FROM [Adventure Works DW2014]

    And the structure of the tables  is like this.

    create table FactTableSales1
    (
     [View] varchar(20),
     [Month] varchar(64),
     Sales int
    )
    
    insert into FactTableSales1 values
    ('A','201901',10),
    ('A','201902',15),
    ('A','201903',5),
    ('A','201904',20),
    ('B','201901',5),
    ('B','201902',10),
    ('B','201903',15),
    ('B','201904',5)
    
    create table DimMonth
    (
    [Month] varchar(64),
    [Short Month] varchar(20)
    )
    
    insert into DimMonth values
    ('201901','01'),
    ('201902','02'),
    ('201903','03'),
    ('201904','04')
    
    
    
    create table DimView
    (
     [View] varchar(20)
    )
    
    insert into DimView values
    ('A'),
    ('B')

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, August 19, 2019 9:17 AM
  • Hi Will,

    Thanks for the response and apologies for my delay in replying!

    I am working on another project at the moment but I will try your suggestion when I move back onto the one that I needed help with.

    Cheers,

    Matty

    Sunday, September 8, 2019 6:27 PM