locked
How do I add filter expression to a calculated member RRS feed

  • Question

  • Experience Level: Beginner

    I am having a problem with establishing correct syntax even after viewing syntax examples.

    I have a calulated member created which performs a distinct count on measure A. This works fine.

    I now want to add  a filter expression to the calculated member based on  measure B which is > 0.

    Filter ( [Measures].[A]

    ( ([Measures].[B]) > 0 )

    Please advise


    ITProTek
    Tuesday, October 18, 2011 3:01 PM

Answers

  • Can you simply include only fact rows where No Charge Amount is > 0 in the cube - or do you need to load the other fact rows as well? In the former case, you can filter the fact table in the cube Data Source View (DSV) by using a Named Query, and then the distinct count will be filtered as you want.
    - Deepak
    • Marked as answer by ITProTek Monday, October 24, 2011 8:26 PM
    Thursday, October 20, 2011 5:11 AM
  • In the Data Source View (DSV), you can right-click on the fact table (dbo.FactTicketSales) and select: "Replace Table ->With New Named Query ..". Then in the Named Query you can add the where clause from the above query as below - for the Ticket Count you can create a cube "distinct count" measure on the  TicketNumberUnique field of this Named Query:

    SELECT

    *

    FROM

    dbo.FactTicketSales

    WHERE

    NOCHARGEAMOUNT > 0and WorkDateKey>'20110930'


    - Deepak
    • Marked as answer by ITProTek Monday, August 12, 2013 6:26 PM
    Tuesday, October 25, 2011 7:04 PM

All replies

  • Hi,

    I am not sure what exactly you want to achieve but you probably can try - Filter ( [Measures].[A], [Measures].[B] > 0 ). Please let me know if you need further information.

    Regards,
    Santosh


    It feels good if you give us some points for helpful post. :)
    Tuesday, October 18, 2011 3:12 PM
  • You can create a named set for all the dimension members for which the measure B > 0 and then use that set against measure A. For example, here is the MDX query to get the Internet sales amount (Measure A) for all the products (Dimension member) which has more than 1000 Quantity (Measure B)

    WITH SET QuantityGreater1000 AS 
    FILTER([Product].[Product Categories].[Product].Members,Measures.[Internet Order Quantity] > 1000)
    
    SELECT {Measures.[Internet Order Quantity],[Measures].[Internet Sales Amount]} ON 0,
    NON EMPTY QuantityGreater1000 On 1 
    FROM [Adventure Works]
    


     


    Hope this Helps!
    Tuesday, October 18, 2011 3:22 PM
  • Santosh,

    I tested the script syntax in my calculations tab, However,  I receive a return #VALUE in the Cube browser. 

    Here is the error I received:

    The Filter function expects a tuple set expression for the 1 argument. A string or numeric expression was used.

    Please advise.

     

     


    ITProTek

    • Edited by ITProTek Tuesday, October 18, 2011 6:10 PM
    Tuesday, October 18, 2011 5:29 PM
  • It will be helpful if you could post the exact code for [Measures].[A] you have in mind. Could you also explain the results you want, with a specific example - keeping in mind that Filter() applies to a set, but [Measures].[A] is not a set?
    - Deepak
    • Edited by Deepak Puri Tuesday, October 18, 2011 6:29 PM
    Tuesday, October 18, 2011 6:28 PM
  • Experience Level: Beginner - Doin' my best

    Measure A = # of Tickets - Distinct Count on Ticket Number

    Measure B = No Charge Tickets (Total Dollars)

    DimLocation Hierarchy used for Region, City, Location EquipID

    Current working environment in Browse tab:

    Filter - Equip Brand - Brand XXX

    Region          City                  Location                  Equip ID               #ofTickets                NoChargeAmount

    New York     New Jersey        Hill Street                CNT809                     400                          2400

                                                  Washington            CNT802                      200                         1200

    *******************************************************************************************************

    Desired:

    The only colum to change will be #ofTickets with a new count filtered on nonempty and >0 on the NoChargAmount.

    #ofTickets is a calculated member based on the measure used as #ofTickets expression.

    I want to derive a new count based on the count of rows for NoChargeAmount nonempty and >0

    Currently I am using this expression by Santosh but to no avail:

    Filter ( [Measures].[A], [Measures].[B] > 0 )

    I receive a return #VALUE in the Cube browser as the result on the derived #ofTickets column against all locations. 

    Here is the error I received:

    The Filter function expects a tuple set expression for the 1 argument. A string or numeric expression was used.

     

     


    ITProTek


    • Edited by ITProTek Tuesday, October 18, 2011 7:55 PM
    Tuesday, October 18, 2011 7:12 PM
  • You can try iif() instead of filter(), like:

    iif([Measures].[B] > 0,  [Measures].[A], null)


    - Deepak
    Wednesday, October 19, 2011 12:21 AM
  • Deepak,

    I tried:

    iif([Measures].[B] > 0,  [Measures].[A], null)

    as you suggested  -  That also returned #VALUE #of ticket value for each LocationID's

    **************************************************************************.

    Is there a way to run 'SELECT' as an expression to obtain my results?

    Another way to look at what I need:

    SELECT

    [Measures].[# Tickets]                      -------This measure is already based on distinct count by LocationID

    FROM CUBENAME                                         -------This is the CUBE NAME being used 

    WHERE [Measures].[No Charge Amount]>0       ------- This measure is based on sum -------I need to test each row for >0 value.

    I only want the '# of Tickets' count returned based on the associated tickets which have a no charge amount greater than >0 


    ITProTek
    Wednesday, October 19, 2011 7:54 PM
  • Could you post the MDX code for [Measures].[# Tickets] - also the cube structure, in terms of tickets, locations, etc? When you talk about testing each row, are you referring to members of some dimension attribute  - a concrete example with specific data would help.


    - Deepak
    Wednesday, October 19, 2011 8:40 PM
  • Everything is in place and working correctly on the MDX Query. EXCEPT Now I need the calculated member  

    '#  Tickets' Needs to adjust to the row count of No Charge Amount >0

    # Tickets currently is  distinct count of all TicketNumbers.

    No Charge Amount  does not reflect distinct count of all tickets. No Charge Amount is all TicketNumbers amounts with a code 9. Which is a subset of All TicketNumbers.

    No Charge Amount Values with a code of 9 are determined in the query at time of data import. I need this count reflected in '# Tickets'.  That is why I am trying to use the Expression field located in the calculated member       '# Tickets' to  create the new total based on the row coutn of No Charge Amount >0.

     


    ITProTek

    • Edited by ITProTek Wednesday, October 19, 2011 11:11 PM
    Wednesday, October 19, 2011 10:49 PM
  • That's an MDX query using  [Measures].[# Tickets] , but what's the MDX definition - is it defined in the cube MDX script? And which client tool are using - is it a version of Excel? And is there a Ticket dimension, where each member corresponds to a ticket - otherwise how are the tickets counted?
    - Deepak
    Wednesday, October 19, 2011 11:09 PM
  • I will be glad to provide this, however, Specifically what MDX Script are you speaking my cube only puts out xmsla?

     

    Client

    I am using the Browse tab in Visual Studio to develop.

    I will be using Excel as a client piece. 2007,2010 (this is where the above query derived)

     

    Dimension

    There is no Ticket dimension only Ticket count in the Fact Table.

    FactTable

    [TicketNumberUnique] in the FactTable is named as '# of Tickets' measure as distinct count

     

    Calculated Member

    '# Tickets' uses the expression [Measures].[# of Tickets]


    ITProTek
    Wednesday, October 19, 2011 11:43 PM
  • So calculated measure: [# Tickets] is currently set to the cube distinct count: [Measures].[# of Tickets]. What about [Measures].[No Charge Amount] - is it a cube "sum" measure based on the same field in the fact table? And should the filter that you want to use: [No Charge Amount] > 0 apply to each row of the fact table - or should it be applied at cube summary level? If you can give a simple example of how this filter should apply to some fact rows, that would help clarify the desired logic.
    - Deepak
    Thursday, October 20, 2011 1:17 AM
  • Below are the answers to the quesitons above:

    1. So calculated measure: [# Tickets] is currently set to the cube distinct count: [Measures].[# of Tickets]? Correction - Calculated Member [# Tickets] is currently set to [Measures].[# of Tickets] in the expression field.  [Measures].[# of Tickets]  is based on the Fact table column name TicketnumberUnique which is set to distinct count.                                      

    2. What about [Measures].[No Charge Amount] - is it a cube "sum" measure based on the same field in the fact table? YES

    3. And should the filter that you want to use: [No Charge Amount] > 0 apply to each row of the fact table - or should it be applied at cube summary level? I believe this is correct,  I am not sure of the terminology cube summary level. 

    If you can give a simple example of how this filter should apply to some fact rows, that would help clarify the desired logic. 

     If No Charge Amount fact row is > 0 then, count 1 and add to total count, else don't count. 

    So that when applied to Locationid hierarchy it represents the correct count by Region-CityId etc... .

    This is already occurs with the calculated member # tickets except with all tickets not just where No Charge Amount is > 0 count.



    ITProTek
    • Edited by ITProTek Thursday, October 20, 2011 2:08 AM
    Thursday, October 20, 2011 1:53 AM
  • Can you simply include only fact rows where No Charge Amount is > 0 in the cube - or do you need to load the other fact rows as well? In the former case, you can filter the fact table in the cube Data Source View (DSV) by using a Named Query, and then the distinct count will be filtered as you want.
    - Deepak
    • Marked as answer by ITProTek Monday, October 24, 2011 8:26 PM
    Thursday, October 20, 2011 5:11 AM
  • this sql query statemtent returns what I need.  

    SELECT

    COUNT(DISTINCT TicketNumberUnique) as

    COUNT

    FROM

    dbo.FactTicketSales

    WHERE

    NOCHARGEAMOUNT > 0 and WorkDateKey > '20110930'

     

    How do I utilize this as a named query?

     


    ITProTek
    Monday, October 24, 2011 7:46 PM
  • In the Data Source View (DSV), you can right-click on the fact table (dbo.FactTicketSales) and select: "Replace Table ->With New Named Query ..". Then in the Named Query you can add the where clause from the above query as below - for the Ticket Count you can create a cube "distinct count" measure on the  TicketNumberUnique field of this Named Query:

    SELECT

    *

    FROM

    dbo.FactTicketSales

    WHERE

    NOCHARGEAMOUNT > 0and WorkDateKey>'20110930'


    - Deepak
    • Marked as answer by ITProTek Monday, August 12, 2013 6:26 PM
    Tuesday, October 25, 2011 7:04 PM