Creating measure that acts on all fields

[ajk_powerbi]

I imported an excel file with, say, 10 fields on it.  Some of the fields have blank values, some don't.  I want to create a measure that calculates the percentage of blanks across ALL fields, per field.  So, for example, if none of the fields have blanks, the report would show 0% across all fields.  I'm able to create a measure that calculates this on one field.  Is it possible to write something like this that acts on all fields, or would it have to be created separately for each field?

This is the measure I created for one field:

BlankValuePercent =

DIVIDE(

CALCULATE(COUNT(Excel_Spreadsheet1[FIELD_A]), Excel_Spreadsheet1[FIELD_A] = ""),

COUNT(Excel_Spreadsheet1[FIELD_A])

)

[parry2k]

@ajk_powerbi you can create one measure and store value in VAR (variables) for each column and then return the result or create 10 measures, there is no other way to achieve it, basically you have to check for each column.

[ajk_powerbi]

@parry2k  Thank you!  Can you confirm how the return would be written?  Here is what I came up with based on your feedback:

% Blank Values =
VAR VFIELD1 = DIVIDE(
                            CALCULATE(COUNT(Excel_SpreadSheet1[FIELD1]), Excel_SpreadSheet1[FIELD1] = ""),
                            COUNT(Excel_SpreadSheet1[FIELD1])
                            )
VAR VFIELD2 = DIVIDE(
                            CALCULATE(COUNT(Excel_SpreadSheet1[FIELD2]), Excel_SpreadSheet1[FIELD2] = ""),
                            COUNT(Excel_SpreadSheet1[FIELD2])
                            )
RETURN
VFIELD1;VFIELD2  <------ This syntax is incorrect, but I would want my graph to show all fields.  How do I write the return to show all fields?  Thank you.

[ajk_powerbi]

@parry2k  the '&' compiles correctly, but now I'm getting errors based on converting type integers and text.  Microsoft Excel seems significantly more intuitive than Power BI, no?

% Blank Values =
VAR VFIELD1 = DIVIDE(
                            CALCULATE(COUNT(Excel_SpreadSheet1[FIELD1]), Excel_SpreadSheet1[FIELD1] = ""),
                            COUNT(Excel_SpreadSheet1[FIELD1])
                            )
VAR VFIELD2 = DIVIDE(
                            CALCULATE(COUNT(Excel_SpreadSheet1[FIELD2]), Excel_SpreadSheet1[FIELD2] = ""),
                            COUNT(Excel_SpreadSheet1[FIELD2])
                            )
RETURN
VFIELD1&VFIELD2

[parry2k]

@ajk_powerbi use format function to convert number to text.

[ajk_powerbi]

@parry2k  Thank you, but more detail please, see below:

% Blank Values =
VAR VFIELD1 = DIVIDE(
                            CALCULATE(COUNT(Excel_SpreadSheet1[FIELD1]), Excel_SpreadSheet1[FIELD1] = ""),
                            COUNT(Excel_SpreadSheet1[FIELD1])
                            )
VAR VFIELD2 = DIVIDE(
                            CALCULATE(COUNT(Excel_SpreadSheet1[FIELD2]), Excel_SpreadSheet1[FIELD2] = ""),
                            COUNT(Excel_SpreadSheet1[FIELD2])
                            )
RETURN
VFIELD1&FORMAT(VFIELD2, ?????)

[parry2k]

@ajk_powerbi 

FORMAT( VFIELD1, "General Number") &
FORMAT( VFIELD2, "General Number") 

[ajk_powerbi]

@parry2k 

This is the error message I get when I use that format with "General Number"

Error Message:
MdxScript(Model) (10, 92) Calculation error in measure 'Calc Measured'[% Blank Values]: DAX comparison operations do not support comparing values of type Integer with values of type Text. Consider using the VALUE or FORMAT function to convert one of the values.

[parry2k]

@ajk_powerbi not sure why, can you share pbix file, remove any sensitivie information before sharing.