ALLEXCEPT - my trojan friend

[craasp]

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

PLEASE HELP!! and Thank you in advance, I think I miss here somthing obvious :((

[Vvelarde]

@craasp

hi, are you expected this result?

[craasp]

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

[Vvelarde · Accepted solution]

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

[craasp]

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. 

[tringuyenminh92]

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

[craasp]

I really would like to understand..

but anyway, your workaround works and i highly appreciate your answer!! Thank you!

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

[tringuyenminh92]

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)

[craasp]

Hi Tri,

thank you for your reply! 

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

[tringuyenminh92]

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.

[craasp]

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

[tringuyenminh92]

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?

[Sean]

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

[craasp]

it stays. but using the measure NEWallApples without ALLEXCECPT

[Sean]

Okay so have you solved the problem then?

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