Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
sid_ks
Helper I
Helper I

Measure skips blank when used in a Matrix visual

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:

1.JPG         2.JPG

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:
3.JPG
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)
4.JPG
 
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!
1 ACCEPTED SOLUTION
v-stephen-msft
Community Support
Community Support

Hi @sid_ks ,

 

You can try to check Show items with no data.

Screenshot 2021-04-14 104435.pngScreenshot 2021-04-14 104448.png

 

 

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.

View solution in original post

4 REPLIES 4
v-stephen-msft
Community Support
Community Support

Hi @sid_ks ,

 

You can try to check Show items with no data.

Screenshot 2021-04-14 104435.pngScreenshot 2021-04-14 104448.png

 

 

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.

Thanks Stephen, that seems to be doing the trick.

amitchandak
Super User
Super User

@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

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! 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.