Count rows with Multiple Conditions

[Anonymous]

Hi

Im new to Power BI and have been stuck on this problem for a few days. 

I can do what im after in Excel just not Power Bi.

I have attached sample data to show where im trying to get to rather than having to explain. 

Basically i have 1 table and from the fact table i want to know the best way to go about counting ID's if it = "X" and count the "ID's" if count of of X is >1. and display this value in a card visual and be able to filter by months on the dashboard. 

https://drive.google.com/open?id=1mrLGAp015rGkkUZwxiOFhAc2gYsH9TrB

Any help or advice welcome! 

[Anonymous]

Hi i was wondering if you could help me with a another measure. 

Based on this measure, instead of counting the ID, can we sum up the Total for any total >1 ?

More than 1 Consultation =
VAR Shortlist =
FILTER(
SUMMARIZE(
'Table',
'Table'[ ID ],
"Total", COUNT( 'Table'[ ID ] )
),
[Total] > 1
)
RETURN
COUNTROWS(Shortlist)

[dm-p · Accepted solution]

Hi @Anonymous,

I've downloaded your data and had a go. This is one way to solve it - create the following measure:

More than 1 Consultation = 
    VAR Shortlist =
        FILTER(
            SUMMARIZE(
                'Table',
                'Table'[ ID ],
                "Total", COUNT( 'Table'[ ID ] )
            ),
            [Total] > 1
        )
    RETURN 
        COUNTROWS(Shortlist)

Here's my table by ID with the measure, and a card:

This will be filter aware on the date, so if I change the slider, I get a revised count, e.g.:

If you were to use a date table to group by month, this will aggregate as required.

Hopefully this should be all you need. Good luck!

Daniel

---

If my post solves your challenge, then please consider accepting as a solution to help other forum members find the answer more quickly 🙂

[Anonymous]

AWESOME WORK Daniel! Thanks for your help, Exactly what i was after!

Thanks so much!