cancel
Showing results for
Did you mean:
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

Accepted Solutions
Super Contributor

## Re: ALLEXCEPT - my trojan friend

@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

Proud to be a Datanaut!

14 REPLIES 14
New Contributor

## Re: ALLEXCEPT - my trojan friend

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

## Re: ALLEXCEPT - my trojan friend

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

New Contributor

## Re: ALLEXCEPT - my trojan friend

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

## Re: ALLEXCEPT - my trojan friend

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

New Contributor

## Re: ALLEXCEPT - my trojan friend

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

## Re: ALLEXCEPT - my trojan friend

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

Super Contributor

## Re: ALLEXCEPT - my trojan friend

@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

## Re: ALLEXCEPT - my trojan friend

it stays. but using the measure NEWallApples without ALLEXCECPT

New Contributor

## Re: ALLEXCEPT - my trojan friend

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?

Announcements

#### Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

#### Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

#### Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 76 members 1,151 guests
Recent signins: