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

  • parry2k's avatar
    parry2k
    Icon for Super User rankSuper User
    7 years ago

    ajk_powerbi 

     

    FORMAT( VFIELD1, "General Number") &
FORMAT( VFIELD2, "General Number")
    • ajk_powerbi's avatar
      ajk_powerbi
      Regular Visitor
      7 years ago

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

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