Creating measure that acts on all fields

Question

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 · Answer

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 · Answer

parry2k&nbsp; Thank you!&nbsp; Can you confirm how the return would be written?&nbsp; Here is what I came up with based on your feedback:&nbsp;% Blank Values =VAR VFIELD1 = DIVIDE(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; CALCULATE(COUNT(Excel_SpreadSheet1[FIELD1]), Excel_SpreadSheet1[FIELD1] = ""),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; COUNT(Excel_SpreadSheet1[FIELD1])&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; )VAR VFIELD2 = DIVIDE(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; CALCULATE(COUNT(Excel_SpreadSheet1[FIELD2]), Excel_SpreadSheet1[FIELD2] = ""),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; COUNT(Excel_SpreadSheet1[FIELD2])&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; )RETURNVFIELD1;VFIELD2&nbsp; &lt;------ This syntax is incorrect, but I would want my graph to show all fields.&nbsp; How do I write the return to show all fields?&nbsp; Thank you.

ajk_powerbi · Answer

parry2k&nbsp; the '&amp;' compiles correctly, but now I'm getting errors based on converting type integers and text.&nbsp; Microsoft Excel seems significantly more intuitive than Power BI, no?&nbsp;% Blank Values =VAR VFIELD1 = DIVIDE(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; CALCULATE(COUNT(Excel_SpreadSheet1[FIELD1]), Excel_SpreadSheet1[FIELD1] = ""),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; COUNT(Excel_SpreadSheet1[FIELD1])&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; )VAR VFIELD2 = DIVIDE(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; CALCULATE(COUNT(Excel_SpreadSheet1[FIELD2]), Excel_SpreadSheet1[FIELD2] = ""),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; COUNT(Excel_SpreadSheet1[FIELD2])&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; )RETURNVFIELD1&amp;VFIELD2

parry2k · Answer

ajk_powerbi use format function to convert number to text.
&nbsp;

ajk_powerbi · Answer

parry2k&nbsp; Thank you, but more detail please, see below:&nbsp;% Blank Values =VAR VFIELD1 = DIVIDE(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; CALCULATE(COUNT(Excel_SpreadSheet1[FIELD1]), Excel_SpreadSheet1[FIELD1] = ""),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; COUNT(Excel_SpreadSheet1[FIELD1])&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; )VAR VFIELD2 = DIVIDE(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; CALCULATE(COUNT(Excel_SpreadSheet1[FIELD2]), Excel_SpreadSheet1[FIELD2] = ""),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; COUNT(Excel_SpreadSheet1[FIELD2])&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; )RETURNVFIELD1&amp;FORMAT(VFIELD2, ?????)

Forum Discussion

Creating measure that acts on all fields

8 Replies

Related content

Need help creating a measure

Help to create DAX Measure

Can i use a measure selected in a slicer (created with field parameters) to create a rank?

Create a dynamic measures using field parameter measure values

Create a Measure from Field Parameters