Forum Discussion

Sander800's avatar
Sander800
Helper I
9 years ago
Solved

Remove filter in If statement

Hi All,

 

Hope you can help me out with this one. I am trying to create 2 sets/ groupings of accounts which I want to show in a table in the rows. However the second set is part of the first set hierarchically speaking. 

 

In the way I have set it up (below) I will not see anything in my second set 'some fixed costs' because these have already been filtered out by my first condition (parent level). 

 

I am looking for a way to 'remove' the filter applied in the first selection so that the second statement can filter on an unfiltered dataset. I have tried all kinds of combinations with all, allexcept and filter but cannot figure it out. Hope you can help

 

1) All Fixed Costs

2) Some Fixed costs

 

Measure=
IF (
    PL_Blink_Cube[SCOA Level06 ID] = 4500000000;
    "Fixed Expenses";
     IF (
          PL_Blink_Cube[SCOA Level10 ID] = 64405401;
          "Some Fixed Costs";
   BLANK ()
)
)
)

 

What I get 
Fixed Expenses500
Some Fixed Costs 
  
What I want 
Fixed Expenses500
Some Fixed Costs20

 

Need Help
Tutorial Requests
  • robofski's avatar
    robofski
    9 years ago

    How about creating two calculated columns:

     

    Total Costs = IF(PL_Blink_Cube[SCOA Level06 ID] = 4500000000, PL_Blink_Cube[Amount],BLANK())

    Some Costs = IF(PL_Blink_Cube[SCOA Level10 ID] = 64405401, PL_Blink_Cube[Amount],BLANK())

     

    This way you keep the rest of the table and have all your relationships.

11 Replies

  • vanessafvg's avatar
    vanessafvg
    Community Champion
    9 years ago

    Sander800 

    maybe its better to use a switch statement

     

    switch(true(),

    and,  (PL_Blink_Cube[SCOA Level06 ID] = 4500000000 ,PL_Blink_Cube[SCOA Level10 ID] <> 64405401), "fixed costs",
     and,  PL_Blink_Cube[SCOA Level06 ID] = 4500000000 ,PL_Blink_Cube[SCOA Level10 ID]  = 64405401), "some fixes costs",

       BLANK ()
    )

     

    havent tested it

    • Sander800's avatar
      Sander800
      Helper I
      9 years ago

      Hi Vanesse,

       

      Thanks for your reply, but this does not work,

       

      A single value for column 'SCOA Level10 ID' in table 'PL_Blink_Cube' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

      • vanessafvg's avatar
        vanessafvg
        Community Champion
        9 years ago

        Sander800 are you creating a measure or a column?  are these in the same table?

         

        also i see a syntax error

        test =
        SWITCH (
            TRUE (),
            AND (
                PL_Blink_Cube[SCOA Level06 ID] = 4500000000,
                PL_Blink_Cube[SCOA Level10 ID] <> 64405401
            ), "fixed costs",
            AND (
                PL_Blink_Cube[SCOA Level06 ID] = 4500000000,
                PL_Blink_Cube[SCOA Level10 ID] = 64405401
            ), "some fixes costs",
            BLANK ()
        )