Forum Discussion

Chanleakna123's avatar
Chanleakna123
Post Prodigy
7 years ago
Solved

Count Distinct turns different when we filter ,

 

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

Tutorial Requests
  • Anonymous's avatar
    Anonymous
    7 years ago

    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

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    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