Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Count columns based on condition per row

For a dataset I desire to calculate the percentage of the available fields that contain data per row.   Based on the Values that have been provided per ID the outcome that is required is to find pe...
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi Anonymous ,

     

    You need to first replace all "null" in the Value(1-200) column with "@" (or other unique symbols or text are also available).

     

    Select the first three columns and select Unpivot other columns

    Measure = 
var _total=CALCULATE(COUNTROWS('HBG110_BAY 13_Testversie ifc'),ALLEXCEPT('HBG110_BAY 13_Testversie ifc','HBG110_BAY 13_Testversie ifc'[Variables]))
var _withNA=CALCULATE(COUNTROWS('HBG110_BAY 13_Testversie ifc'),FILTER(ALLEXCEPT('HBG110_BAY 13_Testversie ifc','HBG110_BAY 13_Testversie ifc'[Variables]),[Value]="N/A"))
var _withnull=CALCULATE(COUNTROWS('HBG110_BAY 13_Testversie ifc'),FILTER(ALLEXCEPT('HBG110_BAY 13_Testversie ifc','HBG110_BAY 13_Testversie ifc'[Variables]),[Value]="@"))
return DIVIDE(_withnull,_total-_withNA)

    The measured value is the same as the expected result.

     

    Best Regards,

    Stephen Tao

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

mahoneypat's avatar
mahoneypat
Microsoft Employee
5 years ago

Here is one way to do it in a calculated column.  I called your table Count, so replace that throughout with your actual table name.

 

NewColumn =
VAR rowtable =
    FILTER (
        { 'Count'[Value 1], 'Count'[Value 2], 'Count'[Value 3] },
        [Value] <> "N/A"
    )
RETURN
    DIVIDE (
        COUNTROWS ( FILTER ( rowtable, [Value] <> "" ) ),
        COUNTROWS ( rowtable )
    )

 

Pat

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    My problem lies with the part of that code I have copied below:

        

    FILTER (
            { 'Count'[Value 1], 'Count'[Value 2], 'Count'[Value 3] },
            [Value] <> "N/A"
        )

    There are over 300 columns in my dataset, would that mean I have to add all 300 columns to this line?