Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 Expenses | 500 |
Some Fixed Costs | |
What I want | |
Fixed Expenses | 500 |
Some Fixed Costs | 20 |
Solved! Go to Solution.
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.
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
Proud to be a Super User!
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
Proud to be a Super User!
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 |
@Sander800,
The only way I can think of is to create a new table using DAX below.
Table 2 = SUMMARIZE(PL_Blink_Cube,"Fixed Expenses",CALCULATE(SUM(PL_Blink_Cube[Amount]),FILTER(PL_Blink_Cube,PL_Blink_Cube[SCOA Level06 ID]=4500000000)),"Some fixed costs",CALCULATE(SUM(PL_Blink_Cube[Amount]),FILTER(PL_Blink_Cube,AND(PL_Blink_Cube[SCOA Level06 ID]=4500000000,PL_Blink_Cube[SCOA Level10 ID]=64405401))))
Regards,
Lydia
Hi Lydia,
This really is a big step in the right direction.
However this way, of course, I loose al my relations. How can I add (the other) dimensions to this new table?
What other dimensions do you refer to? Please post expected result here.
Regards,
Lydia
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.
Thanks all,
This really helps me in the right direction. I think this will help me solve me issue.
Again thanks for the help!
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |