Dax query

Hi,vally57 
Thank you for your reply.

Thank you very much for correcting my misunderstanding of the real data

The following is my understanding of your new requirements

You want to filter the [RATING] column in the Audit table

The screening criteria are as follows:

Take Acqua as an example:

Although only have 23 - and 20-year data, but when the slicer selects other years, you also want measure to display data for the most recent year of the selected year, instead of displaying blank

At this point, even if 21 or 22 years is selected, the data for 2019/20 (the most recent and non-empty count) is displayed.

Since data is available for 23 years (NeedsRefinement), data for 2022/23 is displayed

Here's my code:

test_NeedsRefinement = 

VAR CurrentDate = MAX('slicer_'[FISCAL_YEAR_ID])
VAR result=
         CALCULATE(
            COUNT('Audit'[Name]),
                FILTER(
                    ALLEXCEPT('Audit',Audit[Name]),
                   Audit[RATING]="Needs Refinement"&&
                'Audit'[FISCAL_YEAR_ID] = (CurrentDate)
        ))
VAR max_notblank=CALCULATE(MAX('Audit'[FISCAL_YEAR_ID]),FILTER(ALLSELECTED(Audit),[FISCAL_YEAR_ID]<CurrentDate&&'Audit'[FISCAL_YEAR_ID]<>BLANK()))
VAR result1=
        CALCULATE(
                COUNT('Audit'[Name]),
                    FILTER(
                        ALLEXCEPT('Audit',Audit[Name]),
                    Audit[RATING]="Needs Refinement"&&
                    'Audit'[FISCAL_YEAR_ID] =max_notblank

                    
        ))
RETURN

      IF(result=BLANK(),
      result1,result)

test_NeedsImprovement = 

VAR CurrentDate = MAX('slicer_'[FISCAL_YEAR_ID])

VAR result=
         CALCULATE(
            COUNT('Audit'[Name]),
                FILTER(
                    ALLEXCEPT('Audit',Audit[Name]),
                   Audit[RATING]="Needs  Improvement"&&
                'Audit'[FISCAL_YEAR_ID] = (CurrentDate)
        ))
VAR max_notblank=CALCULATE(MAX('Audit'[FISCAL_YEAR_ID]),FILTER(ALLSELECTED(Audit),[FISCAL_YEAR_ID]<CurrentDate&&'Audit'[FISCAL_YEAR_ID]<>BLANK()))

VAR result1=
         CALCULATE(
            COUNT('Audit'[Name]),
                FILTER(
                    ALLEXCEPT('Audit',Audit[Name]),
                   Audit[RATING]="Needs  Improvement"&&
                   'Audit'[FISCAL_YEAR_ID]=max_notblank
                   
               
        ))        
RETURN 
        IF(result=BLANK(),
         result1,result)

Below is my test code, in page2

About your non-empty situation because the field filter was written incorrectly "Needs Improvement"

There are two Spaces in the data you provide, not one

Looking forward to your reply

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.

Best Regards,

Carson Jian,

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.