Measure skips blank when used in a Matrix visual

[sid_ks]

Hello everyone!

I have two tables: 'QTR' and 'ERROR_DATA'. I have been trying to come up with a measure that calculates SUM() on a column from 'ERROR_DATA' for a selected 'QTR' value in a slicer. It also needs to do the same for the previous 3 quarters and then display it in a matrix. For example, if I select '20214' in the slicer, matrix has to display data for 20214,20213,20212,20211.

Below is the sample data:

QTR:                               ERROR_DATA:

         

Everything works fine with my measure unless there is a blank value in one of the previous 3 quarters. Below is the measure:

ERROR_VAL =

var qk_max = max(QTR[QTR_KEY])

var qk_min = IF(VALUE(RIGHT(MAX(QTR[QTR_KEY]),1)) = 4, qk_max - 3, qk_max - 9)

var res = CALCULATE(SUM(ERROR_DATA[ERROR]),FILTER(ERROR_DATA,ERROR_DATA[QTR_KEY] <= qk_max && ERROR_DATA[QTR_KEY] >= qk_min))

return res

This gives me the following result when used in a matrix:

As you can see, the column value for 20213 is missing as it has a blank 'ERROR_DATA'[ERROR] value for 20213. I found a few topics on the forum that handle blanks in a measure, but when I put my measure in an IF statement and use ISBLANK(), it returns all QTR_KEY values instead of the last 3 quarters, like below:

ERROR_VAL =

var qk_max = max(QTR[QTR_KEY])

var qk_min = IF(VALUE(RIGHT(MAX(QTR[QTR_KEY]),1)) = 4, qk_max - 3, qk_max - 9)

var res = CALCULATE(SUM(ERROR_DATA[ERROR]),FILTER(ERROR_DATA,ERROR_DATA[QTR_KEY] <= qk_max && ERROR_DATA[QTR_KEY] >= qk_min))

return IF(isblank(res),"NA",res)

How can I edit my measure so that it only shows 4 quarters at a time including the selected quarter and also does not omit a quarter even though there is a blank value?

Any help is appreciated. TIA!

[Anonymous · Accepted solution]

Hi @sid_ks ,

You can try to check Show items with no data.

Best Regards,

Stephen Tao

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

[sid_ks]

Thanks Stephen, that seems to be doing the trick.

[amitchandak]

@sid_ks , if you select one date period and want to display more, you need an independent date table too.

refer my video on that

https://www.youtube.com/watch?v=44fGGmg9fHI&list=PLPaNVDMhUXGYo50Ajmr4SgSV9HIQLxc8L&index=18

[sid_ks]

Hi Amit. Firstly, thanks for the reply!

I went through the video you posted. Looks like I have my QTR table independent of the ERROR_DATA table. The measure I created is supposed to show data for 4 quarters at one time depending on the slicer selection.

Since both my tables are independent of each other, the above measure works as intended when there is data for the 4 quarters. But whenever a quarter has a blank, it is being skipped when the measure is used in a matrix visual. I tried to handle this in my measure using NA, but that seems to populate all existing quarters instead of the 4 needed.

How do I handle such quarters which have a blank value but need to shown while the measure is used within a matrix visual. I hope I coudl get my point across, please let me know if you need additional info.

Thanks again!