Count of unique based on conditions

[jjacker]

Hi, Everyone

Hope all is well and safe

I need your expertise on how to create a measure in power pivot that will count the number of 'Names' who have 'Passed' ALL the tests.

Here's a table example for reference:


In the example table, Emma and Sophia have passed all subjects and should be count as 1.
On the other hand, Olivia and Ava still have Pending subjects and should count as 0

So my expected output should be like the table below:



Thanks in advance and more powers!!!

Edit: Please see attached file for reference

 

[AlanSidman]

Using Power Query here is the Mcode

let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"City", type text}, {"Subject", type text}, {"Test Status", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if[Test Status]="Passed" then 1 else 0),
    #"Grouped Rows" = Table.Group(#"Added Custom", {"Name", "City"}, {{"Passed", each List.Sum([Custom]), type number}}),
    #"Removed Columns" = Table.RemoveColumns(#"Grouped Rows",{"Name"}),
    #"Added Custom1" = Table.AddColumn(#"Removed Columns", "Custom", each if [Passed]=4 then 1 else 0),
    #"Grouped Rows1" = Table.Group(#"Added Custom1", {"City"}, {{"Count", each List.Sum([Custom]), type number}})
in
    #"Grouped Rows1"

 

[jjacker]

Wow! That was fast and works perfectly! Thank you @AlanSidman

Can we also create the output in pivot measure instead?
I forgot to mention that the count of 'Name' who passed for each subject will be used on the power pivot.

The Power Pivot should look like the screenshot below, and then the measure that I cannot do is highlighted in yellow.


I have attached the updated sample file for reference.

Thanks again.

P.S. I'm really a beginner with power tools, please forgive me for my questions. If you have feedback/better practices on what I've done with the measures from the attached, I would really like to hear that.

 

[AlanSidman]

Have you tried joining the two tables in the relationship area of Power Pivot?

 

[Fleetspark]

I haven't tried it. I only have one table which is this:

 

[AlanSidman]

The second table is the one created by the Power Query.

 

[Fleetspark]

Got it! Thanks for explaining.

But, would it still be efficient if the table has millions of rows? (Coz im expecting that'll be)

So when achieving the pivot result, does creating another table vs creating a measure would eat the same memory/loading power?

Thanks again @AlanSidman

 

[Chihiro]

DAX measure can be done as following:

Completed :=
VAR temp =
    SUMMARIZE (
        Table2,
        [City],
        [Name],
        "PassCount", CALCULATE ( COUNTROWS ( Table2 ), FILTER ( Table2, [Test Status] = "Passed" ) )
    )
VAR CountComplete =
    CALCULATE ( DISTINCTCOUNT ( [Name] ), FILTER ( temp, [PassCount] = 4 ) )
RETURN
    IF ( ISBLANK ( CountComplete ), 0, CountComplete )

Though I'd recommend like Alan indicated, to use two tables for this. It will be easier to maintain and probably more performant on interaction with the pivot table.

NOTE: This assumes every name should have 4 tests. If that can vary, I'd definitely go with Alan's method.

 

[jjacker]

Can't say a word @Chihiro but this Dax measure works like a magic

Really don't know how the magic works but I hope I can be like you someday.

Thanks to you both @Chihiro and @AlanSidman