Count Distinct with Data Filtering

[AZL]

Greetings,

Guys, I have the folloiwng problem:

I have the follwoing dataset

and here is the output chart

As you notice for item "B" the count distinct without filtering the status is "2",wheres with status filtering it gives a total of "3"

what I need to do is to apply the status filter but I want to get the same total distinct number without the filter.

In other words, if the input data is available in Completed status i dont want to to be counted in Pending status.

For example in case of Area "B" in my dataset:

I have two dataset under Completed status which are 1 and 22 so distinct count should be 2 (which is correct)

now for pending status there's only one dataset which already avialabe in completed status. Hence I want the output to be zero.

Is there any way to do that in one chart??

[v-jiascu-msft]

Hi @AZL ,

Try this measure, please.

Measure 2 =
VAR hasCompletedPending =
    SELECTCOLUMNS (
        FILTER (
            SUMMARIZE (
                'Table1',
                Table1[Data],
                Table1[Status],
                "statusNum", CALCULATE ( COUNT ( Table1[Data] ), ALL ( Table1[Status] ) )
            ),
            [statusNum] >= 2
        ),
        "data", [Data]
    )
RETURN
    IF (
        MIN ( Table1[Status] ) = "Pending",
        CALCULATE (
            COUNT ( Table1[Data] ),
            FILTER ( Table1, NOT [Data] IN hasCompletedPending )
        ),
        COUNT ( Table1[Data] )
    )

Best Regards,

[AZL]

Greetings,

@v-jiascu-msft 

I tried it and i'm getting the following error:

"A single value for column 'Status' in table 'table1' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result."

I'm trying to understand the script but I couldn't understand why did you use ( [statusNum]>=2),"data" )

I will appreciate it if you can attach a power bi file with the script for more explanation.

Thank you

[v-jiascu-msft]

Hi @AZL ,

Could you please mark the proper answers as solutions?

Best Regards,

[AZL]

@v-jiascu-msft 

Sorry....I forgot to update it last time I logged in.

Thank you so much for the solution. 

[v-jiascu-msft · Accepted solution]

Hi @AZL ,

Please download it from the attachment.

In [statusNum]>=2, "=2" means there are "Completed" and "Pending" at the same time. ">2" means there could be a third status just in case.

Best Regards,