cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Qotsa
Helper IV
Helper IV

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.

 

Qotsa_1-1630575730875.png

 

 

 

 

 

 

1 ACCEPTED SOLUTION
VahidDM
Community Champion
Community Champion

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:

VahidDM_0-1630588017527.png

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

Appreciate your Kudos VahidDM_1-1630588045331.png !!

View solution in original post

5 REPLIES 5
Qotsa
Helper IV
Helper IV

@VahidDM That works perfectly. TY.

@PaulDBrown Thanks also.

VahidDM
Community Champion
Community Champion

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:

VahidDM_0-1630588017527.png

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

Appreciate your Kudos VahidDM_1-1630588045331.png !!

View solution in original post

Qotsa
Helper IV
Helper IV

Apologies. Previous PBIX incorrect.

 

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

 

Qotsa
Helper IV
Helper IV

@PaulDBrown  PBIX file ->

 

I think a measure would be best.

 

PaulDBrown
Super User
Super User

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

Do you need a measure or calculated column?





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.