topic Re: Creating measure that acts on all fields in DAX Commands and Tips

Creating measure that acts on all fields

ajk_powerbi — Wed, 10 Jul 2019 21:32:04 GMT

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])

)

Re: Creating measure that acts on all fields

parry2k — Thu, 11 Jul 2019 05:21:12 GMT

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.

Re: Creating measure that acts on all fields

ajk_powerbi — Thu, 11 Jul 2019 14:27:40 GMT

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.

Re: Creating measure that acts on all fields

ajk_powerbi — Thu, 11 Jul 2019 16:21:18 GMT

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?

Re: Creating measure that acts on all fields

parry2k — Thu, 11 Jul 2019 16:23:40 GMT

ajk_powerbi use format function to convert number to text.

Re: Creating measure that acts on all fields

ajk_powerbi — Thu, 11 Jul 2019 16:26:11 GMT

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

Re: Creating measure that acts on all fields

parry2k — Thu, 11 Jul 2019 16:28:47 GMT

ajk_powerbi

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

Re: Creating measure that acts on all fields

ajk_powerbi — Thu, 11 Jul 2019 16:32:09 GMT

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.

Re: Creating measure that acts on all fields

parry2k — Thu, 11 Jul 2019 16:34:16 GMT

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