Forum Discussion

nikhil425's avatar
nikhil425
Helper I
7 years ago

Dynamic Field Counts Without Blanks (Table)

Hello there, 

 

Can any one please help me! I have a table in Power BI with more than 100 columns/fields. Table gets refreshed ofter with SSAS Tabular live connection.

 

The requirement is to count the Rows of each field (without Blanks). If it is just few fields, I can write DAX for each field to count the Non Blank rows. However its across whole table. Is there a way to do it using DAX?

Above screen shot is the output I would like to show.

 

Thanking you,

Boez   

Need Help

3 Replies

    • nikhil425's avatar
      nikhil425
      Helper I
      7 years ago

      Source Data is a just table with rows and columns, From the sample screen shot below, I have to count all the values except Blanks. 

       

       

      • TeigeGao's avatar
        TeigeGao
        Solution Sage
        7 years ago

        Hi nikhil425 ,

        We can do it in Power Query. Please refer to the following steps:

        First we can create a function in Power Query like below:

        let 
  countif = (table_content as table, column_name as text) as number =>
    let
      count_rows = Table.RowCount(Table.SelectRows ( table_content, each Record.Field(_,column_name) <> null))
    in
      count_rows
in
    countif

        The above function will count not null rows for a column, then we can use the function for each column using the following M query:

        let
    Source = ***,
    #"Changed Type" = ***,
    Header = Table.ColumnNames(Source),
    Totals = Table.FromRows({List.Transform(Header, each Query1(#"Changed Type",_))}, Header)
in
    Totals

        The result will like below:

        Please refer to the following pbix file: https://1drv.ms/u/s!Ao9Of0JgO6MU72UvnGI7ONURDYSH

        Best Regards,

        Teige