Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
StephenGW
Helper II
Helper II

Ignore Slicer in Measure

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
1 ACCEPTED SOLUTION
StephenGW
Helper II
Helper II

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

View solution in original post

11 REPLIES 11
StephenGW
Helper II
Helper II

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

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

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
Solution Sage
Solution Sage

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

@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.

 

StephenGW_1-1623790606012.png

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

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

 

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

 

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

@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.

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

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.

@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

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

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