Skip to main content
cancel
Showing results for 
Search instead 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

Reply
craasp
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 :((

1 ACCEPTED SOLUTION
Vvelarde
Community Champion
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

View solution in original post

14 REPLIES 14
Vvelarde
Community Champion
Community Champion

@craasp

 

hi, are you expected this result?

 

result.png




Lima - Peru

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

Bildschirmfoto 2017-01-25 um 13.13.07.png

 

 

 

 

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
Community Champion
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

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
Memorable Member
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 

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

 

 

 

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

 

 

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)

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

 

 

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.

 

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

 

 

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?

Sean
Community Champion
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?

craasp
Frequent Visitor

it stays. but using the measure NEWallApples without ALLEXCECPT

Sean
Community Champion
Community Champion

Okay so have you solved the problem then?

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

Top Solution Authors