Distinct Count with exceptions

[Qotsa]

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.

[Qotsa]

@VahidDM That works perfectly. TY.

@PaulDBrown Thanks also.

[VahidDM · Accepted solution]

Hi @Qotsa 

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  !!

[Qotsa]

Apologies. Previous PBIX incorrect.

Correct PBIX -> https://www.dropbox.com/s/h4fbc0m2gfg2b86/Client%20Status%20Change.pbix?dl=0

[Qotsa]

@PaulDBrown  PBIX file ->

I think a measure would be best.

[PaulDBrown]

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

Do you need a measure or calculated column?