DAX Query

Question

I have created a dax query to get count of names,&nbsp;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=&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;CALCULATE(CALCULATE(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; COUNT('Audit'[ENGAGEMENT_NAME]),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; FILTER(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ALLEXCEPT('Audit','Audit'[ENGAGEMENT_NAME]),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;'Audit'[RATING]="Adequate"&amp;&amp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'Audit'[FISCAL_YEAR_ID] = (CurrentDate)&nbsp; &nbsp; &nbsp; &nbsp; )),&nbsp; &nbsp; &nbsp; &nbsp; CROSSFILTER('Date'[Date],'Audit'[ENGAGEMENT_PLANNED_START],None))&nbsp;&nbsp;VAR max_notblank=CALCULATE(CALCULATE(MAX('Audit'[FISCAL_YEAR_ID]),FILTER(ALLSELECTED(Audit),'Audit'[FISCAL_YEAR_ID]&lt;CurrentDate&amp;&amp;'Audit'[FISCAL_YEAR_ID]&lt;&gt;BLANK())),CROSSFILTER('Date'[Date],Audit[ENGAGEMENT_PLANNED_START],None))VAR result1=CALCULATE(&nbsp; &nbsp; &nbsp; &nbsp; CALCULATE(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; COUNT('Audit'[ENGAGEMENT_NAME]),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; FILTER(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ALLEXCEPT('Audit','Audit'[ENGAGEMENT_NAME]),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Audit[RATING]="Adequate"&amp;&amp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'Audit'[FISCAL_YEAR_ID] =max_notblank&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; &nbsp; &nbsp; &nbsp; )),CROSSFILTER('Date'[Date],Audit[ENGAGEMENT_PLANNED_START],none))RETURN&nbsp; &nbsp; &nbsp;&nbsp;IF(result=BLANK(),&nbsp; &nbsp; &nbsp; 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=&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;CALCULATE(CALCULATE(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; COUNT('Audit'[ENGAGEMENT_NAME]),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; FILTER(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ALLEXCEPT('Audit','Audit'[ENGAGEMENT_NAME]),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;'Audit'[RATING]="Adequate"&amp;&amp;&nbsp;'Audit'[Session] = (SelectedSession)&amp;&amp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'Audit'[FISCAL_YEAR_ID] = (CurrentDate)&nbsp; &nbsp; &nbsp; &nbsp; )),&nbsp; &nbsp; &nbsp; &nbsp; CROSSFILTER('Date'[Date],'Audit'[ENGAGEMENT_PLANNED_START],None))&nbsp;&nbsp;VAR max_notblank=CALCULATE(CALCULATE(MAX('Audit'[FISCAL_YEAR_ID]),FILTER(ALLSELECTED(Audit),'Audit'[FISCAL_YEAR_ID]&lt;CurrentDate&amp;&amp;'Audit'[Session] = (SelectedSession)&amp;&amp;'Audit'[FISCAL_YEAR_ID]&lt;&gt;BLANK())),CROSSFILTER('Date'[Date],Audit[ENGAGEMENT_PLANNED_START],None))VAR result1=CALCULATE(&nbsp; &nbsp; &nbsp; &nbsp; CALCULATE(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; COUNT('Audit'[ENGAGEMENT_NAME]),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; FILTER(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ALLEXCEPT('Audit','Audit'[ENGAGEMENT_NAME]),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Audit[RATING]="Adequate"&amp;&amp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'Audit'[FISCAL_YEAR_ID] =max_notblank&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; &nbsp; &nbsp; &nbsp; )),CROSSFILTER('Date'[Date],Audit[ENGAGEMENT_PLANNED_START],none))RETURN&nbsp; &nbsp; &nbsp;&nbsp;IF(result=BLANK(),&nbsp; &nbsp; &nbsp; result1,result)Please help me by correcting my dax.TIA

anonymous · Accepted Answer

Hi&nbsp; Sri57&nbsp;,
&nbsp;
Try to modify your formula like below:
&nbsp;
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" &amp;&amp; 
        'Audit'[Session] = SelectedSession &amp;&amp; 
        'Audit'[FISCAL_YEAR_ID] = CurrentDate
    )

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

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

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

&nbsp;
Best Regards,Adamk Kong
&nbsp;
If this post&nbsp;helps, then please consider&nbsp;Accept it as the solution&nbsp;to help the other members find it more quickly.

Forum Discussion

DAX Query

2 Replies

Related content

DAX Query help

Dax query dependency

DAX Query Format Date

Dax Queries for Open Close stock

DAX Query View