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 nr. will get a Book nr. However, at the time when the data is extracted from the database, I will count the Req nr. to see how many of them that has not got any Book nr. at all.

 

 

Row nr.        Req nr      Book nr      Timestamp

1123 11.08.2018
2123 12.08.2018
3123 13.08.2018
4456 01.08.2018
545678902.08.2018
645678903.08.2018
7678 20.08.2018
8678 21.08.2018
9888 01.07.2018
1088832102.07.2018
1188832103.07.2018

 

 

Example: Req nr. 123 has not got any Book nr. and should meet the criteria to be counted, Req nr. 456 got a book nr. at row 5 and even though the Req nr. 456 has a blank Book nr. on row 4, it should not be counted. Req. nr 678 does not have a book nr. and should be counted, and last Req nr 888 has a book nr in row 10 and should not be counted. In this example the calculation should give the number 2 – only Req nr. 123 and 678 has not a book nr. at all and should be counted.

Appreciate any idea!

 

Regards

Amund

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

3 Replies

  • 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