Forum Discussion
Remove filter in If statement
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.
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.
Sander800 are you creating a measure or a column? are these in the same table?
also i see a syntax error
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 ()
)
Unfortunately this still does not work.
The initial filter on Level 06 overrules the selections I want to apply on Level 10. So I do see the the 'fixed costs' but not the 'some fixed costs'.
The problem remains the same. I understand the difficulty since I filter on two different levels so the real trick is to undo a the filter on level 6 and then reapply a 'clean' filter on level 10, within the same column basically.
Sander800,
Could you please share sample data of your table? Which column do you use to calculate 500 and 20?
Regards,Hi,
See the below the table. As you can see there are multiple columns as part of this hierarchy. I want to show in 2 rows the total costs (col SCOA Level06 ID) and in the row below 'some fixed costs' (account 64405401 Col SCOA Level10 ID).
Hope this clarifies.
Thanks
SCOA Level06 ID SCOA Level07 ID SCOA Level08 ID SCOA Level09 ID SCOA Level10 ID Amount 4500000000 4510000000 4511000000 4511100000 64405401 20 4500000000 4510000000 4511000000 4511100000 64405402 25 4500000000 4510000000 4511000000 4511100000 64405403 30 4500000000 4510000000 4511000000 4511200000 64405404 10 4500000000 4510000000 4511000000 4511200000 64405405 5 4500000000 4510000000 4511000000 4511200000 64405406 100 4500000000 4510000000 4512000000 4512100000 64405407 45 4500000000 4510000000 4512000000 4512100000 64405408 70 4500000000 4510000000 4512000000 4512100000 64405409 70 4500000000 4510000000 4512000000 4512200000 64405410 30 4500000000 4520000000 4521000000 4521100000 64405411 30 4500000000 4520000000 4521000000 4521100000 64405412 30 4500000000 4520000000 4521000000 4521100000 64405413 10 4500000000 4520000000 4521000000 4521100000 64405414 10 4500000000 4520000000 4521000000 4521200000 64405415 15