Dax required.

[Kiranbc]

I have a requirement of dax in powerbi where I need count of subjects which have just "not missing" value in result. subjects with missing and not missing should not be counted. Belos is the sample data. I did summarize table but it is not working properly. Please help.

Subject Result
0360129-2001 Not missing
0360129-2002 Not missing
1240004-0001 Not missing
1240004-0002 Not missing
1240004-0003 Not missing
1240004-1001 Not missing
1240004-1006 Not missing
1240004-2001 Not missing
1240023-1002 Missing
1240023-1002 Not missing
1240025-0001 Missing
1240025-0001 Not missing
1240100-0002 Missing
1240100-0002 Not missing
1240100-0003 Missing

[v-junyant-msft · Accepted solution]

Hi @Kiranbc ,

Please see is that what you want?
You can use this DAX to create a measure:

SubjectsToExclude = 
CALCULATE(
    DISTINCTCOUNT('Table'[Subject]),
    FILTER(
        ALL('Table'),
        CALCULATE(
            COUNTROWS('Table'),
            'Table'[Result] = "Not missing"
        ) > 0 
        &&
        CALCULATE(
            COUNTROWS('Table'),
            'Table'[Result] <> "Not missing"
        ) = 0
    )
)

And the final output is as below:

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