Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Distinct Count with exceptions

Hi,

 

I have 2 tables related by the Client id column. Table 'Client Table' & 'Status Change Table'.

 

I need a distinct count of Client id from the 'Client Table'.

I only want to distinct count the Client id under the following condition.

 

If the Status_ID or the Current_Status_ID columns in the 'Status Change Table' ever contain 5 then do not distinct count Client id in the 'Client Table'.

 

Desired result shown below.

 

 

 

 

 

 

 

Need Help
  • VahidDM's avatar
    VahidDM
    4 years ago

    Hi Anonymous 

    Link to download the file: https://gofile.io/d/cVkADd

    Try this code to add a column to your Client table:

     

    Result = 
VAR _Count =
    COUNTROWS (
        CALCULATETABLE (
            EXCEPT (
                VALUES ( Client[Client_ID] ),
                SUMMARIZE (
                    FILTER (
                        'Status Change',
                        'Status Change'[Status_ID] = 5
                            || 'Status Change'[Current_Status_ID] = 5
                    ),
                    Client[Client_ID]
                )
            ),
            ALLEXCEPT ( Client, Client[Client_ID] )
        )
    )
RETURN
    IF ( ISBLANK ( _Count ), 0, _Count )

     

     

    Output:

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    Appreciate your Kudos  !!

5 Replies

  • PaulDBrown's avatar
    PaulDBrown
    Community Champion
    4 years ago

    can you share sample data or a PBIX file (through Ondrive, Dropbox...)?

    Do you need a measure or calculated column?

  • VahidDM's avatar
    VahidDM
    Super User
    4 years ago

    Hi Anonymous 

    Link to download the file: https://gofile.io/d/cVkADd

    Try this code to add a column to your Client table:

     

    Result = 
VAR _Count =
    COUNTROWS (
        CALCULATETABLE (
            EXCEPT (
                VALUES ( Client[Client_ID] ),
                SUMMARIZE (
                    FILTER (
                        'Status Change',
                        'Status Change'[Status_ID] = 5
                            || 'Status Change'[Current_Status_ID] = 5
                    ),
                    Client[Client_ID]
                )
            ),
            ALLEXCEPT ( Client, Client[Client_ID] )
        )
    )
RETURN
    IF ( ISBLANK ( _Count ), 0, _Count )

     

     

    Output:

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    Appreciate your Kudos  !!

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    PaulDBrown  PBIX file ->

     

    I think a measure would be best.