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...
  • 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?

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