How to dynamically change a dimension from within a Member Function (in MDX)

Viewed 23

I've been having a hard time trying to figure out how to dynamically change a dimension from within a member function in MDX.

I have a fact table that contains 2 datetime fields (OperationDate and MeetingDate). I have a Power BI dashboard that has a date slicer which is based on the OperationDate. There's a matrix that has a summary of some measures (OpportunitiesCount, LostCount, WinCount, etc) that is being grouped by Sellers. Something like...

Date Slicer (this is based on OperationDate)

DateFrom: 2020-09-01
DateTo: 2020-09-30

<table>
  <tr>
    <th>SellerName</th>
    <th>OpportunityCount</th>
    <th>LostCount</th>
    <th>WinCount</th>
    <th>MeetingCount</th>
  </tr>
  <tr>
    <td>SellerA</td>
    <td>10</td>
    <td>1</td>
    <td>8</td>
    <td>?</td>
  </tr>
  <tr>
    <td>SellerB</td>
    <td>12</td>
    <td>0</td>
    <td>2</td>
    <td>?</td>
  </tr>
  <tr>
    <td>SellerC</td>
    <td>11</td>
    <td>3</td>
    <td>4</td>
    <td>?</td>
  </tr>
</table>

So the [OpportunityCount], [LostCount] and [WinCount] are based on OperationDate. The formulas for the first three columns are similar, but I'll take as an example the LostCount which goes somewhat like:

([Measures].[RowCount],[RowTypes].[Row Type].&[Lost])

At the UI level, the Date Slicer will do the math based on the OperationDate.

On the other hand, for the [Meeting] since it is based on MeetingDate, here's where I get stuck because I would think or thought that by borrowing the formula that I used for the first three columns it would be somewhat like:

([Measures].[RowCount],[RowTypes].[Row Type].&[Meeting])

But this would not give me the correct number since the Date Slicer is based on the OperationDate and not the MeetingDate.

Also I tried to create them from SSMS MDX editor as Member Functions but they are hard-coded. I would like them to be dynamic. In other words, at run-time if there's the possibility to swap dimensions (OperationDate and OptyMeetingDate).

Hardcoded way:

with MEMBER [Measures].[LostCountOperationDate] AS  
sum(
        (
            [Measures].[RowCount]   
            ,[RowTypes].[Row Type].&[Lost]
            ,([OperationDate].[FullYear].[Day].&[2020-09-01T00:00:00]:[OperationDate].[FullYear].[Day].&[2020-09-30T00:00:00])
        )
    )

MEMBER [Measures].[MeetingCountMeetingDate] AS  
sum(
        (
            [Measures].[RowCount]
            ,[RowTypes].[Row Type].&[Meeting]           
            ,([OptyMeetingDate].[FullYear].[Day].&[2020-09-01T00:00:00]:[OptyMeetingDate].[FullYear].[Day].&[2020-09-30T00:00:00])
        )
    )

QUESTION:

Is there a way to have something like...

with MEMBER [Measures].[RowCountOnAnyDateType] AS  
sum(
        (
            [Measures].[RowCount]   
            ,[RowTypes].[Row Type].&[Lost]
            -- to have either dynamically OperationDate
            ,([OperationDate].[FullYear].[Day].&[2020-09-01T00:00:00]:[OperationDate].[FullYear].[Day].&[2020-09-30T00:00:00])
            -- or OptyMeetingDate
            ,([OptyMeetingDate].[FullYear].[Day].&[2020-09-01T00:00:00]:[OptyMeetingDate].[FullYear].[Day].&[2020-09-30T00:00:00])      
            -- but has to be ONLY one of them based on the date range slicer selected at the UI
        )
    )

Thanks in advance, Felix

0 Answers
Related