Forum Discussion

StephenGW's avatar
StephenGW
Icon for Helper II rankHelper II
5 years ago
Solved

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".    ...
Need Help
AlexisOlson's avatar
AlexisOlson
Icon for Super User rankSuper User
5 years ago

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's avatar
HashamNiaz
Icon for Solution Sage rankSolution Sage
5 years ago

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's avatar
    StephenGW
    Icon for Helper II rankHelper II
    5 years ago

    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

  • StephenGW's avatar
    StephenGW
    Icon for Helper II rankHelper II
    5 years ago

    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's avatar
      v-kelly-msft
      Icon for Community Support rankCommunity Support
      5 years ago

      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's avatar
        m3tr01d
        Icon for Continued Contributor rankContinued Contributor
        5 years ago

        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.