cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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

Accepted Solutions
Highlighted
Microsoft
Microsoft

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
Highlighted
Microsoft
Microsoft

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

Highlighted

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!

Highlighted

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


Highlighted
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

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors