cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
craasp Frequent Visitor
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..

 

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

1 ACCEPTED SOLUTION

Accepted Solutions
Vvelarde Super Contributor
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
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




14 REPLIES 14
tringuyenminh92 New Contributor
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 

Screenshot 2017-01-24 18.39.23.png

 

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

Screenshot 2017-01-24 18.40.05.pngScreenshot 2017-01-24 18.40.11.png

 

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

 

Screenshot 2017-01-24 18.36.22.png

 

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

Best regards
Tri Nguyen

 

 

 

craasp Frequent Visitor
Frequent Visitor

Re: ALLEXCEPT - my trojan friend

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 New Contributor
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)

craasp Frequent Visitor
Frequent Visitor

Re: ALLEXCEPT - my trojan friend

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 New Contributor
New Contributor

Re: ALLEXCEPT - my trojan friend

Hi @craasp,

 

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

craasp Frequent Visitor
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 Smiley Happy

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

 

 

Sean Super Contributor
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?

craasp Frequent Visitor
Frequent Visitor

Re: ALLEXCEPT - my trojan friend

it stays. but using the measure NEWallApples without ALLEXCECPT

tringuyenminh92 New Contributor
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:

 

Screenshot 2017-01-25 00.40.03.png

 

Choose color slicer and select none for year

Choose year slicer and select none for color

 

Is this what you are expecting?

Helpful resources

Announcements
Virtual Launch Event

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

Power BI Helps Homeless and Trouble Youth

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

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 276 members 2,836 guests
Please welcome our newest community members: