Dear Team ,

i want to create a new measure that will behave based on the dimension dropped,

ex. if i added the employee dimension only it will aggregate data from the #Calls Count

but if i added the product dimension it should display # Product Calls at the product level and #Calls Count at the employee level as shown in the screen shot.

hi ,

where you want to view this data in Excel or SSRS report. because in EXCEL MDX query will generate at run time.

Regards,
Manish

Hi Khaled,

According to your description, you want to create a measure which can return different fact data based on the dimension slicing. Right?

In Analysis Service, we can use SCOPE() statement to specify different calculation for different scope.

CREATE MEMBER CURRENTCUBE.MEASURES.[x] AS NULL;
SCOPE(MEASURES.[x]);
    SCOPE([Employee].[Employee].MEMBERS);
        THIS = [Measures].[Call Count];
    END SCOPE;
    SCOPE([Product].[Product].MEMBERS);
        THIS = [Measures].[Product Calls];
    END SCOPE;    
END SCOPE;

Reference:
SCOPE Statement (MDX)

Regards,

Hi ,

if you don't want to use scope, use in this way

                

WITH
SET ClassA AS
filter([Customer].[Customer Geography].[Country],[Measures].[Internet Order Count])

SET ClassB AS
filter([Product].[Product],[Measures].[Internet Order Count] )

MEMBER [Customer].[Customer Geography].ClassA AS
SUM(ClassA)

MEMBER [Product].[Product].ClassB AS
SUM(ClassB)
MEMBER [Measures].[GCount]
AS
CASE
    WHEN [Customer].[Customer Geography].CurrentMember IS [Customer].[Customer Geography].ClassA THEN
         ClassA.Count


    ELSE
         0
END

MEMBER [Measures].[PCount]
AS
CASE

    WHEN [Product].[Product].CurrentMember IS [Product].[Product].ClassB THEN
         ClassB.Count

    ELSE
         0
END


SET ClassRangeG AS
{[Customer].[Customer Geography].ClassA}

SET ClassRangeP AS
{ [Product].[Product].ClassB}

SELECT  {[Measures].[Internet Order Count], [Measures].[Internet Sales Amount], [Measures].[GCount],[Measures].[PCount]} ON COLUMNS,
(ClassRangeG,ClassRangeP) ON ROWS
FROM
[Adventure Works]

as per set (ClassRangeG,ClassRangeP) selection you got count otherwise 0.

Regards,Manish

i try the scope and not working the second expression it seems overwrite the first one only product calls working in both dimension

  SCOPE([Product].[Product].MEMBERS);
        THIS = [Measures].[Product Calls];

excel

So I think Simon was on the right track, but I think how it needs to work is to show the [Product Calls] measure unless we are not looking at an individual product, then we should show the [Call Count] 

eg.

CREATE MEMBER CURRENTCUBE.MEASURES.[x] AS [Measures].[Product Calls];
SCOPE(MEASURES.[x]);
    SCOPE([[Product].[Product].[All]);
        THIS = [Measures].[Call Count];
    END SCOPE;
END SCOPE;

In the past I've used this technique for creating generic Year-to-date measures that work with either the financial or calendar date hierarchies depending on which one the user is looking at. I don't usually use this to swap in completely different measures as I tend to find it confuses people and then I get complaints that the totals don't match the details.