cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
setis Member
Member

Ignoring a filter

Dear experts,

I am trying to create a measure to show me the sales amount per month for all years except 2009 (in my real project will be "current year" but for this excercise this can work)

 

My issue is that I need a slicer with the 2009 year selected. 

 

when I do:

Sales NO CY = 
CALCULATE (
    [# Sales];
    FILTER (
        Sales ;
        NOT ( ( YEAR ( Sales[Order Date] ) ) = 2009 )
    )
)

The column is obviously blank since the selected year on the year slicer is 2009. 

 

If I do:

FILTER (
        ALL(Sales) ;
        NOT ( ( YEAR ( Sales[Order Date] ) ) = 2009 )

I get the same amount on all months and it's not what I'm looking for. 

 

The file that I'm using to illustrate the issue is here:

https://drive.google.com/file/d/1yanrt8O0bkd1jKpx3xfphKCrqgOAsCqu/view?usp=sharing

 

Thanks in advance!

9 REPLIES 9
jbarnes Member
Member

Re: Ignoring a filter

have you tried using the calculate function like this;

sales = calculate(sum(sales), 
             year = 2019)

then using a month variable in your visual to break it out?
 
Thanks
jbarnes Member
Member

Re: Ignoring a filter

the title doesn't really match the content - but if you want a visual to ignore a filter use 'Edit Interactions' under the format tab.

setis Member
Member

Re: Ignoring a filter

Apologies for not explaining myself better in the title. 

 

I can't do it in "Edit interactions" since I will have other columns related to the selected year.

 

In this particular column I'll be using an AVERAGEX of the sales amount but I'll looking for to ignore the sales amount of the current year (or the 2009 year) as per the example attached. 

 

The measure I'm working on on my real report is the following:

 

# Cases Forecast- = 
VAR CasesNoCY =CALCULATE (
    [# Cases];
    FILTER (
        Cases ;
        NOT ( ( YEAR ( Cases[Date] ) ) = YEAR ( TODAY () ) )
    )
)
RETURN
CALCULATE(AVERAGEX(VALUES('Date'[Date]);CasesNoCY); ALL('Date'[Calendar Year]))

The issue is that since in that table I have other columns using data for the current year, I can't filter it out for the visual. 

The reason I need to eliminate the current year is to avoid using the actual Nr of Cases on the past dates for the current year forecast. 

I hope that it makes sense.

Super User IV
Super User IV

Re: Ignoring a filter

Try

Sales NO CY = 
var _year = SELECTEDVALUE('Date'[Year])
return
CALCULATE (
    COUNTROWS ( Sales ),ALL('Date'[Year]),not('Date'[Year] =_year)
)




Did I answer your question? Mark my post as a solution!
In case it does not help, please provide additional information and mark me with @
My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP,
HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Proud to be a Super User! Linkedin


jbarnes Member
Member

Re: Ignoring a filter

try the above - failing that, is this what you're looking for?

Capture.JPG

setis Member
Member

Re: Ignoring a filter

Dear @amitchandak and @jbarnes ,

 

Thanks a lot for your answers. The solution proposed by @amitchandak works perfectly in the example, but I must be doing something wrong in my real project, since I'm still getting blanks. 

 

This is my measure:

 

# Cases Forecast-NotCY = 
VAR Year_selected =
    SELECTEDVALUE ( 'Date'[Calendar Year] )
RETURN
    CALCULATE (
        AVERAGEX (
            VALUES ( 'Date'[Date] );
            CALCULATE (
                [# Cases];
                ALL ( 'Date'[Calendar Year] );
                NOT ( 'Date'[Calendar Year] = Year_selected )
            )
        )
    )

My #Cases measure is just a DISTINTCOUNT for Cases ID. 

 

would you be able to see why this isn't working for me here?

Super User IV
Super User IV

Re: Ignoring a filter

Are you trying to get Avg no cases per day ??





Did I answer your question? Mark my post as a solution!
In case it does not help, please provide additional information and mark me with @
My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP,
HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Proud to be a Super User! Linkedin


Super User IV
Super User IV

Re: Ignoring a filter

then try something like that

Avg Sales NO Day = 
var _year = SELECTEDVALUE('Date'[Year])
return
CALCULATE (
    Sales[# Sales],ALL('Date'[Year]),not('Date'[Year] =_year)
)/CALCULATE (
    count('Date'[Date]),ALL('Date'[Year]),not('Date'[Year] =_year)
)




Did I answer your question? Mark my post as a solution!
In case it does not help, please provide additional information and mark me with @
My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP,
HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Proud to be a Super User! Linkedin


Highlighted
setis Member
Member

Re: Ignoring a filter

Dear @amitchandak 

 

Thanks a lot for your effort. I am still getting blanks with this one. 

 

I'll try to reproduce the issue with a dummy report and post it here. 

Helpful resources

Announcements
Coming Soon: T-Shirt Design Contest

Coming Soon: T-Shirt Design Contest

Keep your eyes open for our upcoming T-shirt design contest!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors
Top Kudoed Authors