Count if a row in a column has a value in it - Calculate column measure

[BlueWhite3699]

Hi Experts

See example sample data below. Property 1135 has partial data for all four utilities and hence the "P", Property 1125 has all Utility data hence the "F" and finally 1110 has no utility data and hence the N in the expected result column. How can i achieve this as a calculated column in Power BI and or Power Query???

Property Code	Elec	Water	Gas	Heat	End Result
1135	25	2	0	3	P
1252	35	11	1	2	F
1110					N

[BlueWhite3699]

Excellent

[rajendraongole1 · Accepted solution]

Hi @BlueWhite3699  - you can create a new calculated column as below:

End Result1 =

VAR NonBlankCount =

    COUNTX(

        {

            IF([Elec] = 0 || ISBLANK([Elec]), BLANK(), [Elec]),

            IF([Water] = 0 || ISBLANK([Water]), BLANK(), [Water]),

            IF([Gas] = 0 || ISBLANK([Gas]), BLANK(), [Gas]),

            IF([Heat] = 0 || ISBLANK([Heat]), BLANK(), [Heat])

        },

        IF(NOT(ISBLANK([Value])), 1, BLANK())

    )

RETURN

    IF(NonBlankCount = 0, "N", IF(NonBlankCount < 4, "P", "F"))

Hope this will gives you the expected result.

[rajendraongole1]

if you got the expected output. Please mark my post as a solution.  it helps others. 

Thank you