Power BI total Count for grouped value based on filter condition

[deepakkumar456]

Hello,
I have a table named XYZ and columns A and B as below - 

A	B
Delhi	1
Delhi	2
Delhi	3
Mumbai	1
Jaipur	2
Jaipur	3
Hyderabad	1
Chennai	2
Nagpur	3
Nagpur	4

I need count of column B grouped by A as below - 

A	Count of B
Delhi	3
Mumbai	1
Jaipur	2
Hyderabad	1
Chennai	1
Nagpur	2

and then need to show the Count of B > 1 in a tile in power bi. Could anyone please help me with this using DAX formula ?
Here result would be 3.

This is very easy in SQL but I am struggling in Power BI.

Thanks in advance !!

[deepakkumar456]

Thank you for helping so quickly 🙂 I really apreciate your efforts on this.

[muhammad_786_1 · Accepted solution]

Hi @deepakkumar456 

You can try this DAX to get your result:

Count_B_Greater_Than_1 = 
CALCULATE(
    COUNTROWS(
        FILTER(
            SUMMARIZE('Table', 'Table'[A], "Count_B", COUNT('Table'[B])),
            [Count_B] > 1
        )
    )
)

Best Regards,
Muhammad Yousaf

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

LinkedIn