cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
nikhil425 Frequent Visitor
Frequent Visitor

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?

Capture.JPG

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

 

Thanking you,

Boez   

3 REPLIES 3
Super User
Super User

Re: Dynamic Field Counts Without Blanks (Table)

What does your source data look like?Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


nikhil425 Frequent Visitor
Frequent Visitor

Re: Dynamic Field Counts Without Blanks (Table)

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. 

 

Capture1.JPG 

TeigeGao Super Contributor
Super Contributor

Re: Dynamic Field Counts Without Blanks (Table)

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:

PBIDesktop_QPGIe0Eyv6.png

PBIDesktop_fQyxNNS44h.png

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

Best Regards,

Teige