Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Dax required.

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



Show and Tell
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Anonymous ,

    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.

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Anonymous ,

    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.