create a measure for a column that contain blank values and return 0 when blank

[amalrio]

Hi Gurus, 

I have a request, hope masteres have some solutions for this. my data is like below. the tabel is only filtered to 82 (there are 120 matrix ids)

I created the below measure

=====

Measure

VAR Total =

CALCULATE (

COUNTA ( 'FACT-Data'[TEXT] ),

//KEEPFILTERS ( 'FACT-Data'[TEXT] <> BLANK () ),

'DIM-Matrics'[MATRIX_ID] = 82

)

VAR Result = IF ( Total = BLANK (), 0, Total )

RETURN

Total

========================

I want a report like below( matrix visual), issue here is I want blue circles to be '0' instead on '1' as value for those year in the original tabel is blank. When I use IF logic in the measure it returns [TEXT] related to all the Matrix IDs to the visual (I only want ID 82 in this tabel) even I do filter out rest of the ids from visual level they still come into the visual. Hope this is clear enoug description of the issue. If not please let me know so I can provide information you need. Thanks a lot in advance.

 

[amitchandak]

@amalrio , Is the text column having null values , or is it coimg because of join.

Try like

Measure

VAR Total =
CALCULATE (
COUNTA ( 'FACT-Data'[TEXT] ),
//KEEPFILTERS ( 'FACT-Data'[TEXT] <> BLANK () ),
'DIM-Matrics'[MATRIX_ID] = 82
)
VAR Result = IF ( Total = BLANK (), 0, Total )
RETURN
if(isblank(Max( 'FACT-Data'[TEXT] )) && [Total]>0, 0, [total])

[amalrio]

@amitchandak ,

Thanks for the reply, but it did not work. it still gives me the zero, in the Powerbi data tabel values are blank see below image.

[Anonymous · Accepted solution]

HI @amalrio,

Did these records really exist in your table? If this is a case, you can add '+0' to your formula to force expand these blank fields with zero.

Or you need to create a table to extract all column field values and use it on your matrix, then you can write a measure formula to calculate results based on the current row and column category and use 'if statement' to replace the blank part to zero.

If you still confused about the coding formula, please are some dummy data with raw table structure to help us clarify your data structure and test to coding formula.

How to Get Your Question Answered Quickly 
Regards,

Xiaoxin Sheng