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

[madhav2020]

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

Thanks in advance for your help

[Fowmy · Accepted solution]

@madhav2020 

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)

________________________

If my answer was helpful, please mark it as a solution

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

YouTube
LinkedIn

[v-alq-msft · Accepted solution]

Hi, @madhav2020 

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

Total Count = 
SUMX(
    ADDCOLUMNS(
        '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.

[pearlking]

What does [Value] mean in your DAX?

[Fowmy · Accepted solution]

@madhav2020 

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)

________________________

If my answer was helpful, please mark it as a solution

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

YouTube
LinkedIn