cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## DAX query to calculate count of filled cells in selected column range

Hey Folks,

Just a quick help needed in counting totals number of filled cells in selected range of columns.

Here is the problem statement.

 Employee Name T100 T200 T300 T400 John Basic rubin kumar Basic Johnathon Steve James Basic Advanced-1 Cherry Rishab Sandra Basic Ali Basic Advanced-2 Annet Intermediate Natasha Basic parveez Basic Intermediate Ibrahim Basic Advanced-1 joseph Basic Arold Basic Advanced-1 Aseen Niraj

I would like to get a total count from column T100 to T400 as Total_Count= 16

2 ACCEPTED SOLUTIONS
Super User

Use this measure to get the count:

``````Measure =

VAR  T =
FILTER(
UNION(
SELECTCOLUMNS('Table',"C1", 'Table'[T100]),
SELECTCOLUMNS('Table',"C2", 'Table'[T200]),
SELECTCOLUMNS('Table',"C3", 'Table'[T300]),
SELECTCOLUMNS('Table',"C4", 'Table'[T400])
),
[C1] <>BLANK()
)

RETURN
COUNTROWS(T)``````

________________________

Click on the Thumbs-Up icon if you like this reply 🙂

Did I answer your question? Mark my post as a solution! and hit thumbs up
Community Support

Based on your description, you may create a measure as below . The pbix file is attached in the end.

``````Total Count =
SUMX(
'Table',
"Count",
var tab = {[T100],[T200],[T300],[T400]}
var result =
COUNTROWS(
FILTER(
tab,
[Value]<>BLANK()
)
)
return
IF(
ISBLANK(result),
0,
result
)
),
[Count]
)``````

Result:

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

3 REPLIES 3
Community Support

Based on your description, you may create a measure as below . The pbix file is attached in the end.

``````Total Count =
SUMX(
'Table',
"Count",
var tab = {[T100],[T200],[T300],[T400]}
var result =
COUNTROWS(
FILTER(
tab,
[Value]<>BLANK()
)
)
return
IF(
ISBLANK(result),
0,
result
)
),
[Count]
)``````

Result:

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Frequent Visitor

What does [Value] mean in your DAX?

Super User

Use this measure to get the count:

``````Measure =

VAR  T =
FILTER(
UNION(
SELECTCOLUMNS('Table',"C1", 'Table'[T100]),
SELECTCOLUMNS('Table',"C2", 'Table'[T200]),
SELECTCOLUMNS('Table',"C3", 'Table'[T300]),
SELECTCOLUMNS('Table',"C4", 'Table'[T400])
),
[C1] <>BLANK()
)

RETURN
COUNTROWS(T)``````

________________________

Click on the Thumbs-Up icon if you like this reply 🙂

Did I answer your question? Mark my post as a solution! and hit thumbs up

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.