Counting Distinct Column then Distinct Row

[Wooticus]

Hello all,

I've dug through the forums to try and narrow down my question, but have yet to find an exact answer. Please see example data here:

What I am trying to do is:

1. Do a distinct count of the ID, so for example it only reads "123" once and so on.

2. After Step 1, only count "Pass", so for this example it would output "2" since "456" and "1234" are both "Pass" and "456" has been only counted once.

This is what I am using so far:

If I try putting in Sheet1[ID] = "Pass" then PowerBI kicks an error saying it expects a column for the second argument. How do I need to rewrite this?

The Excel data I am using is an example from a product that is auto-generated from a program I have no control over, so I cannot change the Excel format. Additionally, I cannot outright eliminate duplicate "ID" for the overall PowerBI product because some portions of my overall product needs to read the duplicate entries.

Thank you and hope I explained this well enough,

Cody Frank

[Wooticus]

Hello all, sorry for the late response. I ended up utilizing this solution which I apologize I cannot find it but it worked well:

Pass = 
CALCULATE(
    DISTINCTCOUNT(Sheet1[ID]),
    FILTER(Sheet1,Sheet1[Rating] = "PASS")
)

[YukiK]

Try a formula like this:

Measure = 
COUNTROWS(
    CALCULATETABLE(
        VALUES(Sheet1[ID]),
        Sheet1[Rating] = "Pass"
    )
)

[jdbuchanan71]

@Wooticus 

Try it like this.

Test1 =
CALCULATE ( DISTINCTCOUNT ( Sheet1[ID] ), Sheet1[Rating] = "Pass" )