Ignore Slicer in Measure

[StephenGW]

Hello,

I read a few similar threads but couldn't find one that would work with my measure. I made this measure using Quick Measure to find the most recent date where a column equals "Fail".

Fail Date =

MAXX(

    FILTER(Data,Data[Pass/Fail]="Fail"),

    CALCULATE(MIN('Data'[Audit Date]))

)+0

How can I make this ignore two slicers just for this measure. I have other data I want to react to the slicers in the same table but not this measure. I also have 2 other slicers I want to work with this measure. I need to ignore a slicer for 'Data'[Audit Month] and 'Data'[Audit year].

Is this possible?

Thanks,

StephenGW

[StephenGW · Accepted solution]

Thank you everyone for the help. I ended up going a different route that can be seen here.

Solved: Re: Count by criteria - Microsoft Power BI Community

StephenGW

[v-kelly-msft]

Hi  @StephenGW ,

Glad to know that your issue is solved now,could you pls mark the reply as answered to let more people find the solution.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[m3tr01d]

You'll need to use either 

ALL( Data[Audit Month], Data [Audit Year]), Data[Pass/Fail]="Fail")

or

ALL( Data[Audit Month], Data [Audit Year]), Data[Audit Date] )

 if you want to remove the filter on Month and Year slicer and also filter Audit_Date or Pass/Fail column

[HashamNiaz]

Hi @StephenGW !

Please try using ALL() function to remove the filter cotext from [Audit Month] & [Audit Year] columns.

Fail Date = CALCULATE(MIN('Data'[Audit Date]), FILTER(ALL(Data, Data[Audit Month], Data [Audit Year]), Data[Pass/Fail]="Fail"))

Regards,

Hasham

[StephenGW]

@HashamNiaz 

Thanks but that makes the visual not display. The details say "The rport measure 'Data'[Fail Date] has a syntax or semantic error at line 1, position 53, reported by Analysis Services: 'Multiple table arguments are not allowed in the ALL function. Here is a screenshot of my filters and the table.

I want the Location and Department slicers to apply to the measure but not the year or month. Only thing in the table is 'Data'[Area] and the measure.

Thanks for the quick reply.

StephenGW

[AlexisOlson]

ALL can take a table or columns but not both. Try removing the first argument in ALL:

ALL ( Data[Audit Month], Data[Audit Year] )

[HashamNiaz]

Thanks @AlexisOlson for correcting it.

@StephenGW So it could be like this;

Fail Date = CALCULATE(MIN('Data'[Audit Date]), FILTER(ALL(Data[Audit Month], Data [Audit Year]), Data[Pass/Fail]="Fail"))

Regards,

Hasham

[StephenGW]

@HashamNiaz  and @AlexisOlson 

This counts what I want but it includes the slicers.

Fail Date =

CALCULATE(COUNTROWS(Data),FILTER(Data,Data[Audit Date]>=MAXX(

FILTER(Data,Data[Pass/Fail]="Fail"),

CALCULATE(MIN('Data'[Audit Date]))

)+0),FILTER(Data,Data[Pass/Fail]="Pass"))

The goal is to count how many Passes are equal to or greater than the most recent Fail date. This does this but I need it to ignore the month and year slicers.

Hope someone can help.

Thanks.

[v-kelly-msft]

Hi @StephenGW ,

Try:

Fail Date = CALCULATE(MIN('Data'[Audit Date]), FILTER(ALL(Data), Data[Pass/Fail]="Fail"))

No need to emphasis 2 fields after All,just choose all the table.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[m3tr01d]

This approach can yield really bad performance if the table has lot of million rows and lot of columns, Alberto Ferrari/Marco Russo always recommends using the minimum number of columns in an ALL Statement.

[StephenGW]

@HashamNiaz  and @AlexisOlson 

I think we are getting close but now it's not seeing the column Pass/Fail in the filter for Data[Pass/Fail]="Fail". In the formula box it underlines it in red and says Cannot find name 'Pass/Fail'. I tried deleting the column and renetering to see what it offered for column names and it did not have any Pass/Fail column. Why is this column now missing?

Edit: If this helps the data is coming from a spreadsheet in my OneDrive for Business

Thanks