Forum Discussion

amuola's avatar
amuola
Icon for Helper II rankHelper II
8 years ago
Solved

Distinct count based on a criteria from another column

  Hi, I would like to count the number of distinct “Req nr” that has not got any “Book nr” at all. Each row in the Table corresponds to a certain activity in the process, and at some time the Req n...
Need Help
  • v-yulgu-msft's avatar
    v-yulgu-msft
    8 years ago

    Hi amuola,

     

    You could add below measure to a card visual to show result.

    DistinctCount =
CALCULATE (
    DISTINCTCOUNT ( 'Book record'[Req nr] ),
    FILTER (
        ALL ( 'Book record' ),
        CALCULATE (
            COUNT ( 'Book record'[Book nr] ),
            FILTER (
                ALLEXCEPT ( 'Book record', 'Book record'[Req nr] ),
                'Book record'[Book nr] <> BLANK ()
            )
        )
            = BLANK ()
    )
)

     

    Best regards,

    Yuliana Gu

v-yulgu-msft's avatar
v-yulgu-msft
Icon for Microsoft Employee rankMicrosoft Employee
8 years ago

Hi amuola,

 

You could add below measure to a card visual to show result.

DistinctCount =
CALCULATE (
    DISTINCTCOUNT ( 'Book record'[Req nr] ),
    FILTER (
        ALL ( 'Book record' ),
        CALCULATE (
            COUNT ( 'Book record'[Book nr] ),
            FILTER (
                ALLEXCEPT ( 'Book record', 'Book record'[Req nr] ),
                'Book record'[Book nr] <> BLANK ()
            )
        )
            = BLANK ()
    )
)

 

Best regards,

Yuliana Gu

  • amuola's avatar
    amuola
    Icon for Helper II rankHelper II
    8 years ago

    excellent, this works perfect!

     

    Can you please briefly describe the steps?

     

    Regards

    Amund

  • amuola's avatar
    amuola
    Icon for Helper II rankHelper II
    8 years ago

    Is it possible to modify the measure so that I can get a distribution over time? Each Req nr has a Req date – the date when the Req nr was generated. The measure gives me the total number of unique Req nr without a book nr (what I wanted), however I would also like to see the numbers over time.

     

    Regards Amund