Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
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
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.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
76 | |
74 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |