Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Sander800
Helper I
Helper I

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

 

1 ACCEPTED 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.

View solution in original post

11 REPLIES 11
vanessafvg
Super User
Super User

@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





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

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

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 ()
)

 





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

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,

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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 IDSCOA Level07 IDSCOA Level08 IDSCOA Level09 IDSCOA Level10 IDAmount
45000000004510000000451100000045111000006440540120
45000000004510000000451100000045111000006440540225
45000000004510000000451100000045111000006440540330
45000000004510000000451100000045112000006440540410
4500000000451000000045110000004511200000644054055
450000000045100000004511000000451120000064405406100
45000000004510000000451200000045121000006440540745
45000000004510000000451200000045121000006440540870
45000000004510000000451200000045121000006440540970
45000000004510000000451200000045122000006440541030
45000000004520000000452100000045211000006440541130
45000000004520000000452100000045211000006440541230
45000000004520000000452100000045211000006440541310
45000000004520000000452100000045211000006440541410
45000000004520000000452100000045212000006440541515

@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))))

1.PNG

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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?

@Sander800,


What other dimensions do you refer to? Please post expected result here.

Regards,

Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.