Forum Discussion

ajk_powerbi's avatar
ajk_powerbi
Regular Visitor
7 years ago

Creating measure that acts on all fields

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 fie...
parry2k's avatar
parry2k
Icon for Super User rankSuper User
7 years ago

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's avatar
ajk_powerbi
Regular Visitor
7 years ago

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's avatar
    ajk_powerbi
    Regular Visitor
    7 years ago

    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

      • ajk_powerbi's avatar
        ajk_powerbi
        Regular Visitor
        7 years ago

        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, ?????)