Using a function returns blank when using another function value

stuartcorns — Thu, 15 Sep 2022 09:08:01 GMT

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

Re: Using a function returns blank when using another function value

daXtreme — Thu, 15 Sep 2022 12:07:23 GMT

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.

topic Re: Using a function returns blank when using another function value in DAX Commands and Tips

Using a function returns blank when using another function value

Re: Using a function returns blank when using another function value