Using a function returns blank when using another function value

[stuartcorns]

Hi, I was wondering if anyone could help - new to the community.

I have some DAX code that should return some student attendance for the previous week. However, I currently have to hard code the previous fiscal week number, when I place the value from the Calendar Dates table (Calculates the previous fiscal week from the current). This my code

Previous Week Attendance Measure = 
var _measure =
DIVIDE(
    CALCULATE(
        COUNT('AT_Session_Marks'[Session Mark]), 
        FILTER(AT_Session_Marks, 'AT_Session_Marks'[Absent] = TRUE), 
        FILTER('AT_Session_Marks', 'AT_Session_Marks'[Fiscal Week No] = 1)
    ), 
    CALCULATE(
        COUNT('AT_Session_Marks'[Session Mark]), 
        FILTER('AT_Session_Marks', 'AT_Session_Marks'[Fiscal Week No] = 1)
    ),
    0)
return
IF(ISBLANK(_measure), 0, _measure)

The above returns the correct amount.

Previous Week Attendance Measure = 
var _measure =
DIVIDE(
    CALCULATE(
        COUNT('AT_Session_Marks'[Session Mark]), 
        FILTER(AT_Session_Marks, 'AT_Session_Marks'[Absent] = TRUE), 
        FILTER('AT_Session_Marks', 'AT_Session_Marks'[Fiscal Week No] = 'Calendar Functions'[Previous Fiscal Week])
    ), 
    CALCULATE(
        COUNT('AT_Session_Marks'[Session Mark]), 
        FILTER('AT_Session_Marks', 'AT_Session_Marks'[Fiscal Week No] = 'Calendar Functions'[Previous Fiscal Week])
    ),
    0)
return
IF(ISBLANK(_measure), 0, _measure)

Whereas the above returns 0 despite the 'Calendar Functions'[Previous Fiscal Week] outputting the correct value.

Appreciate some guidance. TIA

[daXtreme · Accepted solution]

Hi @stuartcorns 

Please change you model as soon as possible to a correct one, meaning a star schema. Your current model is not following Best Practices and therefore you'll very soon have multiple problems (you already have). Please, before you start doing any serious work with PBI, read this: Understand star schema and the importance for Power BI … (bing.com)

Also, you should never filter by a full table, especially by a fact table as this puts a lot of stress on the engine and DAX becomes slow to the point where you're going to get timeouts. The golden rule of DAX is: Never filter a table when you can filter a column.