Filtering out accounts with no contact

[richrich123]

Hello everyone,

Im struggling to get a measure or calculated column to filter down a simple BI table of accounts/contacts  to just show the accounts where there are no registered contacts present. So in this example, Fosters has 1 contact registered and Guinness has 2 contacts registered so both of these accounts can be excluded as compliant. I just need to be left seeing that Peroni and Strongbow have no one registered so they can be contacted and the issue resolved (all accounts need at least one registered contact)

I had tried a calculated column like my example below but it is saying you cant mix text and integer so not sure what the best method is. I basically just need to take this simple BI table view that has approx 11,000 contacts across 300 accounts and reduce it to just to show the account names that have no contacts registered. Hope that makes sense - Thank you!

[Registered?] = // calc column
var __accounts = contacts[account.name]
var __registered =
      NOT ISEMPTY(
            FILTER(
              contacts,
              contacts[account.name] = __account
              &&
              contacts[registered] = "0"
)
)
RETURN
        if( __registered, "0", "1" )

[Anonymous]

Hi @richrich123 ,

I want to confirm your demand with you:

1. Do you want to get the  table 2  from table 1? 

2. Do you want to create a column named  "Registered"? and then to get the table 2

which is correct? or all is wrong?

Thanks for your efforts & time in advance.

Best regards,
Community Support Team_ Binbin Yu

[richrich123]

Hello @Anonymous ,

So I have all the data I need in dbo.table1 (account name, contact name and registered)

I just want to produce a table in PBI that from the data in dbo.table1,  can just show me any customers who DO NOT HAVE any support contacts resistered

So very simply put, select all accounts where there are no contacts that are registered.

[Anonymous · Accepted solution]

Hi @richrich123 ,

Please try below steps

1. I create a table for test

Table:

2. create a new table with below dax formula

Table 2 =
VAR tmp =
    SUMMARIZE (
        'Table',
        'Table'[Account Name],
        "Sum for Registered", SUM ( 'Table'[Registered] )
    )
VAR tmp1 =
    FILTER ( tmp, [Sum for Registered] = 0 )
RETURN
    CALCULATETABLE ( VALUES ( 'Table'[Account Name] ), tmp1 )

Please refer the attached .pbix file.

Best regards,
Community Support Team_ Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[richrich123]

@Anonymous  - That worked perfectly, many thanks for your help!

[PijushRoy]

Hi @richrich123 

Please use the RANKX function 
where no contract i.e. 0 rank should be 1 and other wise more than one

If solve your requirement, please mark this answer as SOLUTION
If this comment helps you, please LIKE this comment/Kudos