DAX count non-blank columns in a range of columns

[tomekm]

Hello,

I'm trying to create a calculated column that will count the number of non-blank/non-zero columns in a range of columns (price 1, price 2, price 3, price 4). My table looks like the below and the end result is in the last column:

project	price1	price2	price3	price4	count of non-blank columns
a	2	5			2
b	3	4	5		3
c	6	7	9	12	4

Thank you.

[muhammad_786_1 · Accepted solution]

Hy @tomekm 

You can use the @lbendlin  solution or, if you prefer, in the calculated column, use this measure.

Count_Non_Blank = 
VAR CountValues = 
    COUNTX(
        { [price1], [price2], [price3], [price4] }, 
        IF(NOT(ISBLANK([Value])) && [Value] <> 0, 1, BLANK())
    )
RETURN 
    CountValues

Best Regards,
Muhammad Yousaf

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

LinkedIn

[muhammad_786_1 · Accepted solution]

Hy @tomekm 

You can use the @lbendlin  solution or, if you prefer, in the calculated column, use this measure.

Count_Non_Blank = 
VAR CountValues = 
    COUNTX(
        { [price1], [price2], [price3], [price4] }, 
        IF(NOT(ISBLANK([Value])) && [Value] <> 0, 1, BLANK())
    )
RETURN 
    CountValues

Best Regards,
Muhammad Yousaf

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

LinkedIn

[tomekm]

Thank you!

[lbendlin · Accepted solution]

@muhammad_786_1 

of course this only works for four hard coded columns that have a number type.

[muhammad_786_1]

Yes, you're right! This works well only for hardcoded columns with a number type. Appreciate your insights as always!

[tomekm]

Thank you!

[lbendlin]

Unpivot and group

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUTICYlMgVgDjWJ1opSQgyxiITeAyINFkIMsMiM2B2BKIDY2UYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [project = _t, price1 = _t, price2 = _t, price3 = _t, price4 = _t]),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"project"}, "Attribute", "Value"),
    #"Changed Type" = Table.TransformColumnTypes(#"Unpivoted Other Columns",{{"Value", Int64.Type}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Value] <> null)),
    #"Grouped Rows" = Table.Group(#"Filtered Rows", {"project"}, {{"Count of non-blank columns", each Table.RowCount(_), Int64.Type}})
in
    #"Grouped Rows"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Once you examined the code, replace the entire Source step with your own source.

[tomekm]

Thank you but I need to create a calculated column instead of making changes in power query. Would you have any suggetions for that?

Cheers.

[lbendlin]

You cannot do that - Power BI does not allow arbitrary numbers of columns. At a minimum you need to unpivot.

A calculated column is calculated for each row of your data.  After unpivoting that row has a different meaning, it is no longer one row per project.