Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
osbrown
Frequent Visitor

Dax function to add a column counting rows that are not null

Hello, i've been getting help from this forum for a long time, and this is my first time needing to ask a quesiton. It's been so helpful. However, i'm stumped on this. I have connected to a text file and filtered the query down to just the data I am needing. Now I'm trying to add a column to the query that will count the number of times a row has data in one of ten columns. I've pasted a table below with a sample of the data. I'm trying to grade the participants on frequency of participation. The values of each column are unique to how they connected to the event so they can all be a little different.

 

Does anyone have a suggestion on this?

 

IDEvent 1Event 2Event 3Event 4 Event 5
1dn321    
2as345as345   
3se356    
4dn321dn321as345dn321dn321
5dn321dn322dn323  
6se356se357se358se359 
7dn321dn322dn323as345se356
1 ACCEPTED SOLUTION
Phil_Seamark
Employee
Employee

HI @osbrown

 

So do you mean, for your row with an ID of 1 should return 1, while the row with an ID of 4 will return 5?


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

View solution in original post

4 REPLIES 4
v-yuta-msft
Community Support
Community Support

Hi osbrown,

 

Based on your description, you want to count nonblack data by each ID, right?

 

To achieve your requirement, please follow steps below:

 

  1. Click Query Editor->Column [ID]->Unpivot Other Columns.1.PNG
  2. The table has been transformed into attribute-value pair, then delete Column [Attribute].2.PNG
  3. To delete rows which contain blank, set filter on column [Value] using formula below.

       = Table.SelectRows(#"Removed Columns", each [Value] <> "")

3.PNG

     4.To count rows by ID, use group by function.

6.PNG

 4.PNG

The result is like below and you can refer to PBIX file here:

https://www.dropbox.com/s/zajw7e5vj7ei1vo/For%20osbrown.pbix?dl=0

5.PNG 

 

Best Regards,

Jimmy Tao

Phil_Seamark
Employee
Employee

HI @osbrown

 

So do you mean, for your row with an ID of 1 should return 1, while the row with an ID of 4 will return 5?


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Hi @osbrown

 

If so, this might be close to what you need.  Just change the Table2 to your own table.

 

New Column = 
    IF(LEN('Table2'[Event 1])>1,1) +
    IF(LEN('Table2'[Event 2])>1,1) +
    IF(LEN('Table2'[Event 3])>1,1) +
    IF(LEN('Table2'[Event 4])>1,1) +
    IF(LEN('Table2'[Event 5])>1,1)  

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Hi @osbrown

 

You can do this using QUERY EDITOR as well.... since your ROW values are all SAME

 

SELECT THE 10 COLUMNS>>>Go to ADD COLUMN tab >>> STATISTICS>>>DISCTINCTCOUNT

 

QueryCount.png

 

Now add a custom column substracting 1 from Distinct Count

 

CustomColumn.png


Regards
Zubair

Please try my custom visuals

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors