From a big list with duplicates, isolating records with certain criteria and counting

[RA_gov]

From TABLE1 containing all banks and their custodians, I need a DAX query(s) that isolates only BANK_NAME where CUSTODIAN_NAME = 'CUSTODIAN' ... and from that listing, shows me counts for all their other custodians.

Restults would look like:

COLORADO BANK    1

MIDWAY BANK           3

UNION BANK                0

Thanks for the help!

RA

TABLE1

BANK_NAME	CUSTODIAN_NAME
UNION BANK	BUSINESS A
COMMUNITY BANK	BUSINESS BB
COLORADO BANK	CUSTODIAN
COLORADO BANK	BUSINESS A
STANDARD BANK	BUSINESS CCC
STANDARD BANK	BUSINESS BB
DOVETAIL S&L	BUSINESS A
MIDWAY BANK	BUSINESS BB
MIDWAY BANK	BUSINESS A
MIDWAY BANK	BUSINESS CCC
MIDWAY BANK	CUSTODIAN
UNION BANK	CUSTODIAN
HAWAII BANK	BUSINESS CCC
FIRST BANK	BUSINESS CCC

[amitchandak]

@RA_gov , Try a measure like

calculate(distinctcount(Table[CUSTODIAN_NAME]),Table[CUSTODIAN_NAME] <>"CUSTODIAN")

[RA_gov]

Nope. 

I Need:         If  BANK_NAME has CUSTODIAN_NAME = 'CUSTODIAN', count all the other custodians for that particular bank

[Anonymous]

@RA_gov 

This should work

Measure 2 =
VAR Custodians =
    COUNTROWS ( FILTER ( 'Table (2)', 'Table (2)'[CUSTODIAN_NAME] = "CUSTODIAN" ) )
RETURN
    IF (
        Custodians >= 1,
        COUNTROWS ( FILTER ( 'Table (2)', 'Table (2)'[CUSTODIAN_NAME] <> "CUSTODIAN" ) ),
        0
    )

[RA_gov]

Not there yet. 

BANK_NAME has to be the result of the VAR phrase, then BANK_NAME VAR joined against the remaining list to pull out those where <>= CUSTODIAN 

I'm too new to DAX to figure this out.  I appreciate your help.

[harshnathani]

Hi @RA_gov ,

The above code will work, if you remove the 0 from the If statement.

Also, your output for Union Bank should be 1 and not 0.

YOu can try this measure too, but maynot be the best solution.

Measure 2 = 

var __TableC =

CALCULATETABLE(VALUES('Table (2)'[BANK_NAME]), 'Table (2)'[CUSTODIAN_NAME] = "CUSTODIAN" )

var __TableNC = 
CALCULATETABLE(VALUES('Table (2)'[BANK_NAME]), 'Table (2)'[CUSTODIAN_NAME] <> "CUSTODIAN" )

RETURN

COUNTX(FILTER('Table (2)' ,'Table (2)'[BANK_NAME] IN INTERSECT(__TableNC,__TableC) && 'Table (2)'[CUSTODIAN_NAME] <> "CUSTODIAN"),'Table (2)'[CUSTODIAN_NAME])

Regards,
Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

[RA_gov]

this works!

Thanks.

I am having issues accepting and posting kudos.  system gives me an error when i attempt to give you kudos.  your solution wins.

Thanks 

[RA_gov]

Thanks, but not the answer I need. 

Yours says:      If BANK has CUSTODIAN, count it

I Need:         If  BANK has CUSTODIAN, count all the other custodians for that particular bank

[Anonymous]

Hi @RA_gov Do as @Greg_Deckler suggest but  change the = sign to <>

But based on your description this should work. 

Measure 2 =
VAR Custodians =
    COUNTROWS ( FILTER ( 'Table (2)', 'Table (2)'[CUSTODIAN_NAME] = "CUSTODIAN" ) )
RETURN
    IF (
        Custodians >= 1,
        COUNTROWS ( FILTER ( 'Table (2)', 'Table (2)'[CUSTODIAN_NAME] <> "CUSTODIAN" ) ),
        0
    )

Appreciate with kudos.

Mark as solution if this resolves your problem.

Thanks

[Greg_Deckler]

whoops

[Greg_Deckler]

@RA_gov - 

Place Bank name and this measure in a visual:

Measure =
  COUNTROWS(FILTER('Table',[CUSTODIAN_NAME] = 'CUSTODIAN'))