Forum Discussion

deepakkumar456's avatar
deepakkumar456
Frequent Visitor
1 year ago
Solved

Power BI total Count for grouped value based on filter condition

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

AB
Delhi1
Delhi2
Delhi3
Mumbai1
Jaipur2
Jaipur3
Hyderabad1
Chennai2
Nagpur3
Nagpur4

 

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

ACount of B
Delhi3
Mumbai1
Jaipur2
Hyderabad1
Chennai1
Nagpur2

 

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 !!

Need Help
  • muhammad_786_1's avatar
    muhammad_786_1
    1 year ago

    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

2 Replies

  • muhammad_786_1's avatar
    muhammad_786_1
    Icon for Solution Supplier rankSolution Supplier
    1 year ago

    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

  • deepakkumar456's avatar
    deepakkumar456
    Frequent Visitor
    1 year ago

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