COUNTA Function based on a range of columns per row

[m_fenn]

There are five columns in my table that represent the Sprints that an Issue Type (Story, Bug, Defect, etc.) appeared in. I am looking for a count to help me understand average number of Sprints an Issue goes through.

In Excel, the equation for my problem is: =COUNTA(IX2:JB2)

Example:

Sprint	Sprint	Sprint	Sprint	Sprint	# of Sprints
Sprint 84	Sprint 85	Sprint 86			3
Sprint 77					1

However, when I try to replicate a similar equation in Power BI, I receive either (1) a syntax error or (2) a summation of all the counts per column, not per column per row.

Can you help me figure out how to solve this issue?

[v-chuncz-msft · Accepted solution]

@m_fenn,

You just need to select Unpivot Columns in Query Editor first.

https://community.powerbi.com/t5/Desktop/matrix-to-table-help/td-p/329415