Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
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.
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.
What does [Value] mean in your DAX?
@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 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
102 | |
53 | |
21 | |
12 | |
12 |