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?

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