Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

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
  • Phil_Seamark's avatar
    Phil_Seamark
    8 years ago

    HI Anonymous

     

    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?

4 Replies

  • Phil_Seamark's avatar
    Phil_Seamark
    Icon for Microsoft Employee rankMicrosoft Employee
    8 years ago

    HI Anonymous

     

    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?

    • Phil_Seamark's avatar
      Phil_Seamark
      Icon for Microsoft Employee rankMicrosoft Employee
      8 years ago

      Hi Anonymous

       

      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)
      • Zubair_Muhammad's avatar
        Zubair_Muhammad
        Icon for Community Champion rankCommunity Champion
        8 years ago

        Hi Anonymous

         

        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

         

         

        Now add a custom column substracting 1 from Distinct Count

         

  • v-yuta-msft's avatar
    v-yuta-msft
    Icon for Community Support rankCommunity Support
    8 years ago

    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.
    2. The table has been transformed into attribute-value pair, then delete Column [Attribute].
    3. To delete rows which contain blank, set filter on column [Value] using formula below.

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

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

     

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

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

     

     

    Best Regards,

    Jimmy Tao