Count Distinct turns different when we filter ,

[Chanleakna123]

hi , i don't know why i filter the Count Distinct turn all different . the total on table is 272 , Open is 86 and close is 200 , 

when i try filter it's open 86 and Closed 200 , but when i unclick and see the table instead , those turn to 272 with Close and Open . 

not sure what happen from the file . can you help me? i shared you below Pbi link 

https://drive.google.com/file/d/1qnR7otuTCxxfA1VOe_leNUKRHtzNorxj/view?usp=sharing

[Anonymous · Accepted solution]

Hi @Chanleakna123,

After play with your sample, I found you are summary 'case id' with 'distinct count' mode.

I think it means your table have 14 cases with same case id has multiple status(opened and closed) at same time(duplicate post?), power bi summary them and remove duplicate part.

You can try to use below measure to find out duplicate case id:

Duplicate Status =
CONCATENATEX (
    FILTER (
        SUMMARIZE (
            ALLSELECTED ( 'Report of Commitee' ),
            [Case ID],
            "Status", COUNTROWS ( VALUES ( 'Report of Commitee'[Status] ) )
        ),
        [Status] > 1
    ),
    [Case ID],
    ","
)

Notice: please do mask on sensitive data before share sample file.

Regards,

Xiaoxin Sheng