Distinct count based on a criteria from another column

[amuola]

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

1	123		11.08.2018
2	123		12.08.2018
3	123		13.08.2018
4	456		01.08.2018
5	456	789	02.08.2018
6	456	789	03.08.2018
7	678		20.08.2018
8	678		21.08.2018
9	888		01.07.2018
10	888	321	02.07.2018
11	888	321	03.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

[v-yulgu-msft · Accepted solution]

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]

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

[amuola]

excellent, this works perfect!

Can you please briefly describe the steps?

Regards

Amund