Dax query

[vally57]

Hello Experts,
I want to write a dax measure for the following scenario:

Here, I have a slicer named "Period Range" that contains data like 2019/2020, 2020/2021, etc., in text format. In the last column, we have NR (Needs Refinement) and NI (Needs Improvement) for all last results available for the YTD (2023/2024) period range. If we do not have data for NR and NI in 2023/2024, it should display data from 2022/2023. Similarly, if we do not have data in 2022/2023, it should display data from 2021/2022, and this should continue until we find the data. If we select the period range 2022/2023 from the slicer, it should check data for 2022/2023. If there is no data in 2022/2023, it should display data from 2021/2022 if it is available.

this should be done dynamically as we have large date range.

[tamerj1]

Hi @vally57 
Hope you're looking for something similar to the following

[v-jtian-msft]

Hi,@vally57 ,I am glad to help you.

According to your description, you want to get the latest non-empty data for each period range.

Here is my test, you can refer to it

Since you didn't provide detailed data, I created test data to test, here is my test data.

M_NR = 
VAR _max_year = MAXX(FILTER(ALL('Tabelle1'),'Tabelle1'[period range]=SELECTEDVALUE(Tabelle1[period range])&&'Tabelle1'[Needs Refinement]<>BLANK()),'Tabelle1'[year_num])
RETURN 
CALCULATE(MAX('Tabelle1'[Needs Refinement]),FILTER(ALL(Tabelle1),'Tabelle1'[year_num]=_max_year&&'Tabelle1'[period range]=MAX(Tabelle1[period range]))
)

M_NI = 
VAR _max_year = MAXX(FILTER(ALL('Tabelle1'),'Tabelle1'[period range]=SELECTEDVALUE(Tabelle1[period range])&&'Tabelle1'[Needs Imporvement]<>BLANK()),'Tabelle1'[year_num])
RETURN 
CALCULATE(MAX('Tabelle1'[Needs Imporvement]),FILTER(ALL(Tabelle1),'Tabelle1'[year_num]=_max_year&&'Tabelle1'[period range]=MAX('Tabelle1'[period range]))
)

Please note that since the above results are from my test environment, it is possible that the data model fields I produced are not consistent with yours, so you can refer to the ideas I provided but the specific implementation code needs to be tweaked by you because the value of the MEASURES will be affected by the filtering in many ways (including the slicer and external filtering fields)

If possible, could you provide the .PBIX file without sensitive data, it will be helpful to solve your problem.

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.

[vally57]

@v-jtian-msft thanks for responding,

Actually, "Needs Improvement" and "Needs Refinement" are not columns; those are values in the column named "Rating." Here, I want the latest non-empty count of the name. For example, if I select 2022/23 from the slicer and do not have "Needs Refinement" or "Needs Improvement" for that particular name, it should get the count of the name from 2021/22.

In the above example, we do not have Acqua data for "Needs Refinement" for the year 2023/24, so we should get the value 2, but I'm getting null. Similarly, if we select 2022/23, we should get 2, but I'm getting 5, which counts the entire date range.

I have added the pbix file 
https://www.dropbox.com/scl/fi/5k46fdr2b2kwrkwyd1mwv/test.pbix?rlkey=gs3e020c91pj4mdwtt5kcsv8t&st=rp...

TIA

[v-jtian-msft · Accepted solution]

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.

[vally57]

@v-jtian-msftIt works if there is no relationship between the Audit and Calendar tables. I want a relationship between these tables, but to ignore it, I used the crossfilter function. However, it is still not working. If I manually remove the relationship, I get the expected result, which I do not want.
test_NeedsRefinement =

VAR CurrentDate = MAX('Calendar'[Fiscal Year ID])

VAR result=

         CALCULATE(

            COUNT('Audit'[Name]),

                FILTER(

                    ALLEXCEPT('Audit','Audit'[Name]),

                   Audit[RATING]="Needs Refinement"&&

                'Audit'[FISCAL_YEAR_ID] = (CurrentDate)

        ),CROSSFILTER('Calendar'[Date],Audit[PLANNED_START],None))

VAR max_notblank=CALCULATE(MAX('Audit'[FISCAL_YEAR_ID]),FILTER(ALLSELECTED(Audit),[FISCAL_YEAR_ID]<CurrentDate&&'Audit'[FISCAL_YEAR_ID]<>BLANK()),CROSSFILTER('Calendar'[Date],Audit[PLANNED_START],None))

VAR result1=

        CALCULATE(

                COUNT('Audit'[Name]),

                    FILTER(

                        ALLEXCEPT('Audit','Audit'[Name]),

                    Audit[RATING]="Needs Refinement"&&

                    'Audit'[FISCAL_YEAR_ID] =max_notblank

        ),CROSSFILTER('Calendar'[Date],Audit[PLANNED_START],None))

RETURN

      IF(result=BLANK(),

      result1,result)
https://www.dropbox.com/scl/fi/5k46fdr2b2kwrkwyd1mwv/test.pbix?rlkey=gs3e020c91pj4mdwtt5kcsv8t&st=3e...
TIA

[vally57]

It works when I use CALCULATE inside another CALCULATE and include the CROSSFILTER function.