DAX Query

[Sri57]

I have created a dax query to get count of names, considering both the current fiscal year and potentially the previous fiscal year if no data exists for the current year.please find the following dax.

Adequate_FYTD_Audit =

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

VAR result=

         CALCULATE(CALCULATE(

            COUNT('Audit'[ENGAGEMENT_NAME]),

                FILTER(

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

                   'Audit'[RATING]="Adequate"&&

                'Audit'[FISCAL_YEAR_ID] = (CurrentDate)

        )),

        CROSSFILTER('Date'[Date],'Audit'[ENGAGEMENT_PLANNED_START],None))  

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

,CROSSFILTER('Date'[Date],Audit[ENGAGEMENT_PLANNED_START],None))

VAR result1=CALCULATE(

        CALCULATE(

                COUNT('Audit'[ENGAGEMENT_NAME]),

                    FILTER(

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

                    Audit[RATING]="Adequate"&&

                    'Audit'[FISCAL_YEAR_ID] =max_notblank                   

        )),CROSSFILTER('Date'[Date],Audit[ENGAGEMENT_PLANNED_START],none))

RETURN

      IF(result=BLANK(),

      result1,result)
Also we have Session column in Audit and Date tables, it contains Session1, Session2 and Session3.I use Seesion column as a slicer from Date table, here when I select Session1 and Fiscal year 2023/24 and there is no data for Session1 in 2023/24, I want most recent year of selected year and selected session i.e., if there is data foe 2022/23 in session 1 then it should show count of engagement_nmae where rating is "Adequate".
Sameway if I select Session2 from year 2022/23 and there is no data to display, then it should check for previous year same session i.e., session2 year 2021/22, if there is no data then check for session2 2020/21 like wise check untill there is data for session 2 and retrieve count of engagement_name.
I have written the below logic for session selection, but I'm not getting count if there is no data. I'm not getting where exactly I missed.

Adequate_FYTD_Audit _Session=

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

VAR SelectedSession = SELECTEDVALUE('Audit'[Session])

VAR result=

         CALCULATE(CALCULATE(

            COUNT('Audit'[ENGAGEMENT_NAME]),

                FILTER(

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

                   'Audit'[RATING]="Adequate"&& 

'Audit'[Session] = (SelectedSession)&&

                'Audit'[FISCAL_YEAR_ID] = (CurrentDate)

        )),

        CROSSFILTER('Date'[Date],'Audit'[ENGAGEMENT_PLANNED_START],None))  

VAR max_notblank=CALCULATE(CALCULATE(MAX('Audit'[FISCAL_YEAR_ID]),FILTER(ALLSELECTED(Audit),'Audit'[FISCAL_YEAR_ID]<CurrentDate&&'Audit'[Session] = (SelectedSession)&&'Audit'[FISCAL_YEAR_ID]<>BLANK()))

,CROSSFILTER('Date'[Date],Audit[ENGAGEMENT_PLANNED_START],None))

VAR result1=CALCULATE(

        CALCULATE(

                COUNT('Audit'[ENGAGEMENT_NAME]),

                    FILTER(

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

                    Audit[RATING]="Adequate"&&

                    'Audit'[FISCAL_YEAR_ID] =max_notblank                   

        )),CROSSFILTER('Date'[Date],Audit[ENGAGEMENT_PLANNED_START],none))

RETURN

      IF(result=BLANK(),

      result1,result)

Please help me by correcting my dax.

TIA

[v-kongfanf-msft · Accepted solution]

Hi  @Sri57 ,

Try to modify your formula like below:

Adequate_FYTD_Audit_Session = 
VAR CurrentDate = MAX('Date'[Fiscal Year ID])
VAR SelectedSession = SELECTEDVALUE('Date'[Session])

VAR result = 
    CALCULATE(
        COUNT('Audit'[ENGAGEMENT_NAME]),
        'Audit'[RATING] = "Adequate" && 
        'Audit'[Session] = SelectedSession && 
        'Audit'[FISCAL_YEAR_ID] = CurrentDate
    )

VAR max_notblank = 
    CALCULATE(
        MAX('Audit'[FISCAL_YEAR_ID]),
        FILTER(
            ALL('Audit'), 
            'Audit'[FISCAL_YEAR_ID] < CurrentDate && 
            'Audit'[Session] = SelectedSession && 
            NOT ISBLANK('Audit'[FISCAL_YEAR_ID])
        )
    )

VAR result1 = 
    CALCULATE(
        COUNT('Audit'[ENGAGEMENT_NAME]),
        'Audit'[RATING] = "Adequate" && 
        'Audit'[Session] = SelectedSession && 
        'Audit'[FISCAL_YEAR_ID] = max_notblank
    )

RETURN
    IF(ISBLANK(result), result1, result)

Best Regards,
Adamk Kong

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

[Sahir_Maharaj]

Hello @Sri57,

Can you please try the following:

Adequate_FYTD_Audit

Adequate_FYTD_Audit =
VAR CurrentDate = MAX('Date'[Fiscal Year ID])
VAR result =
    CALCULATE(
        COUNT('Audit'[ENGAGEMENT_NAME]),
        FILTER(
            ALLEXCEPT('Audit', 'Audit'[ENGAGEMENT_NAME]),
            'Audit'[RATING] = "Adequate" &&
            'Audit'[FISCAL_YEAR_ID] = CurrentDate
        ),
        CROSSFILTER('Date'[Date], 'Audit'[ENGAGEMENT_PLANNED_START], None)
    )
VAR max_notblank =
    CALCULATE(
        MAX('Audit'[FISCAL_YEAR_ID]),
        FILTER(
            ALLSELECTED('Audit'),
            'Audit'[FISCAL_YEAR_ID] < CurrentDate &&
            NOT(ISBLANK('Audit'[FISCAL_YEAR_ID]))
        ),
        CROSSFILTER('Date'[Date], 'Audit'[ENGAGEMENT_PLANNED_START], None)
    )
VAR result1 =
    CALCULATE(
        COUNT('Audit'[ENGAGEMENT_NAME]),
        FILTER(
            ALLEXCEPT('Audit', 'Audit'[ENGAGEMENT_NAME]),
            'Audit'[RATING] = "Adequate" &&
            'Audit'[FISCAL_YEAR_ID] = max_notblank
        ),
        CROSSFILTER('Date'[Date], 'Audit'[ENGAGEMENT_PLANNED_START], None)
    )
RETURN
    IF(ISBLANK(result), result1, result)

Adequate_FYTD_Audit_Session

Adequate_FYTD_Audit_Session =
VAR CurrentDate = MAX('Date'[Fiscal Year ID])
VAR SelectedSession = SELECTEDVALUE('Date'[Session])
VAR result =
    CALCULATE(
        COUNT('Audit'[ENGAGEMENT_NAME]),
        FILTER(
            ALLEXCEPT('Audit', 'Audit'[ENGAGEMENT_NAME]),
            'Audit'[RATING] = "Adequate" &&
            'Audit'[Session] = SelectedSession &&
            'Audit'[FISCAL_YEAR_ID] = CurrentDate
        ),
        CROSSFILTER('Date'[Date], 'Audit'[ENGAGEMENT_PLANNED_START], None)
    )
VAR max_notblank =
    CALCULATE(
        MAX('Audit'[FISCAL_YEAR_ID]),
        FILTER(
            ALLSELECTED('Audit'),
            'Audit'[FISCAL_YEAR_ID] < CurrentDate &&
            'Audit'[Session] = SelectedSession &&
            NOT(ISBLANK('Audit'[FISCAL_YEAR_ID]))
        ),
        CROSSFILTER('Date'[Date], 'Audit'[ENGAGEMENT_PLANNED_START], None)
    )
VAR result1 =
    CALCULATE(
        COUNT('Audit'[ENGAGEMENT_NAME]),
        FILTER(
            ALLEXCEPT('Audit', 'Audit'[ENGAGEMENT_NAME]),
            'Audit'[RATING] = "Adequate" &&
            'Audit'[Session] = SelectedSession &&
            'Audit'[FISCAL_YEAR_ID] = max_notblank
        ),
        CROSSFILTER('Date'[Date], 'Audit'[ENGAGEMENT_PLANNED_START], None)
    )
RETURN
    IF(ISBLANK(result), result1, result)

Hope this helps!