cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## ALLEXCEPT - my trojan friend

Hi, I struggle with ALLEXCEPT in one of my measures, here is what I want:

Example table "Apples":

 Year Color Size Amount 2007 green big 10 2007 red small 20 2007 red big 20 2008 green big 10 2008 green small 10 2008 red big 20 2008 red small 20 2009 green big 10 2009 yellow small 10 2009 red small 20 2010 green small 10

I like to visualise on a line and clustered column chart, "allApples" and "selectedApples" side by side with line representing the ratio of "allApples" to "selectedApples" (="ratio"); x-axis are the Years. "selectedApples" is the the sum of Amount per Year, which should react to a slicer where I can choose in two slicers Color and Size. "allApples" are the sum of Amount per Year without reacting to the slicer. But the one yellow apple should be filtered out by an page level filter.

My solution attempt:

allApples=Calculate(sum(Apples[Amount]);Allexcept(Apples;Apples[Year]);Filter(Apples;Apples[Color]<>"yellow"))

selectedApples= sum(Apples[Amount])

ratio=Apples[selectedApples]/Apples[allApples]

remark: the orginaal table got more columns, data entries and slicer. This is just a simplisitc example

Here is how it turns out: The slicer for Color affects also allApples. Why?? Now I would assume that is related to the filter in "allApples" measure, but the slicer for Size does also affect "allApples"

A not sufficient Solution:

when I incorporate the ALLEXCEPT in a filter:  allApples=Calculate(sum(Apples[Amount]);Filter(Allexcept(Apples;[Year]);Apples[Color]<>"yellow")) it seems to work.. but:  when I choose only red apples in the slicer, the data for 2010 is not displayed for "allApples" because "selectedApples" does not have any data. How to prevent this?????????? I choose already "show items with no data" with the x-axis..

1 ACCEPTED SOLUTION
Community Champion

@craasp

Hi, to obtain this chart (in a preview Post) i modified the measure to this:

```AllApples =
CALCULATE (
SUM ( Apples[Amount] ),
ALLEXCEPT ( Apples, Years[Year] ),
Apples[Color] <> "yellow"
)```

Lima - Peru
14 REPLIES 14
Community Champion

@craasp

hi, are you expected this result?

Lima - Peru
Frequent Visitor

Hi, sorry if i kept you waiting..

@tringuyenminh92 some how like in your picture, in reagard to the case that allApples stay while selected Apples = 0. But in my case, the interaction of the slicer of Years is only used for other visualisation on the same page. allApples should be displayed in every year! The interaction are set right.

@Vvelarde, yes, that should look like that.

Two scenarios:

1. I use the Years[Year] data as x-Axis

A: For NEWallApples (=Calculate(sum(Apples[Amount];Apples[Color]<>"yellow";ALL(Apples[Size]))):

The column for respective years stay in case that selectedAppes = 0

but a unpleasant flaw: if I higlight a color (in the real case a country on a map, !not in the legend of the map! (it works with other visualisation e.g. piechart), then the highlighted area/amount equals the highlighted amount of selectedApples

B: For TRIallApples (= Calculate(sum(Apples[Amount]);Filter(Allexcept(Apples;Apples[Size]);Apples[Color]<>"yellow")))

The column for respective years stay in case that selectedAppes = 0

but: it sums all apples for the whole range of years

pro: the flaw of A seems to happen

2. I use Apples[Year] data as x-Axis:

2. Scenario:

For A&B: in the case of selectedApples = 0 --> no column in that year

Picture shows scenario 1

Hope the problem is understandable..

I would just go with scenario 1 A, but I also would like to understand wyh I cant use ALLEXCEPT + this unfortunate flaw bothers me..

Best regards and thanks in advance,

Caspar

Community Champion

@craasp

Hi, to obtain this chart (in a preview Post) i modified the measure to this:

```AllApples =
CALCULATE (
SUM ( Apples[Amount] ),
ALLEXCEPT ( Apples, Years[Year] ),
Apples[Color] <> "yellow"
)```

Lima - Peru
Frequent Visitor

Thank you very much for your replies, @Vvelarde and @tringuyenminh92. I think that will do it. Still, while it works for the small example, in my real project the "allApples" columns disappear when no "selectedApples" are shown for any year. Although I wonder why it differs, the solution is close enough to what I was looking for.

I hope I wont stumble into more problems with that.

For now, again THANKS

ps: I mark @Vvelardes reply as solution, although @tringuyenminh92 mentioned the idea with the years table first, because it shows the solution in most condensed way.

Memorable Member

Hi @craasp,

Your concern is really amazing to me cause i have never noticed about this. So for my solution, I will do like this:

Create Years table: Modeling tab -> New Table -> Years= values(Apples[Year])

Create relationship for Year column between Apples table and Years table

• In format tab, choose Edit Interactions, and choose None for both of 2 slicers Years and Color (to prevent filtering when select 2010 and the color will be filtered  or versus)

So now the filter values of color and years will not be filtered (but the filter expression in your ratio formula is still working)

If this works for you please accept it as solution and also like to give KUDOS.

Best regards
Tri Nguyen

Frequent Visitor

I really would like to understand..

Wormwood: It does not work together with allexcept. (correct me if im wrong)

So here is what I did:

1. following instructions of Mr. Nguyen (new table with column year, and create relationship)

2. replalaced Year from Apples table in the visualisation with the Year from Year table.

3. replace allApples with NEWallApples=Calculate(sum(Apples[Amount];Apples[Color]<>"yellow";ALL(Apples[Size]))

Memorable Member

Hi @craasp,

Yes, you got my point but for the step 3, I'm using your modified expression

`allApples = Calculate(sum(Apples[Amount]),Filter(Allexcept(Apples,Apples[Year]),Apples[Color]<>"yellow")) `

The meaning ALLEXCEPT is allowing filter context affected to your expression result

And my recommendation for divide is using divide method instead of "/"

(And i'm Tri - sounds like Tree or Tea)

Frequent Visitor

Hi Tri,

It doesn´t work here as you say.. it accumulates in each column the Amount of all years (for ALLEXCEPT and Years[Year] as X-axis). I tried the Apples[Year] as x-axis again, but then column with no selectedApples data disappears.

Don´t know why, something gets broken wenn i use allexcept and filter. Tried with the FILTER function and without, ALLEXCEPT  in FILTER and outside, with SUMX and many more desperate combinations..

I go without ALLEXCEPT for now, but any hints and ideas are of course still very welcome!

Best regards, Caspar

Memorable Member

Hi @craasp,

it confuses me now :(, could you please share you pbix file with sample data? And it's better if there is a picture to describe your expectation.

Frequent Visitor

Hey @tringuyenminh92

I didn´t want to confuse you, and even less I want to steal your time :).

Thank you for helping me!

I can´t attach files, but here is the file in my dropbox:

https://www.dropbox.com/s/9x6dsd7xcxr0y4s/Example.pbix?dl=0

Memorable Member

Hi @craasp,

As my observation with your pbix file, I just choose none in interaction of years slicer and color slicer as the picture:

Choose color slicer and select none for year

Choose year slicer and select none for color

Is this what you are expecting?

Community Champion

@craasp

Can you try something...

Create a pie chart with only color and amount!

Then click on red in the Pie Chart not in the Slicer and tell me if the the 2010 column in you line and column chart now remains?

Frequent Visitor

it stays. but using the measure NEWallApples without ALLEXCECPT

Community Champion

Okay so have you solved the problem then?

Are you trying now to get the ratio to extend to 2010?