topic Re: From a big list with duplicates, isolating records with certain criteria and counting in DAX Commands and Tips

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

RA_gov — Wed, 05 Aug 2020 11:51:58 GMT

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!

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

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

Greg_Deckler — Wed, 05 Aug 2020 11:59:57 GMT

RA_gov -

Place Bank name and this measure in a visual:

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

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

RA_gov — Wed, 05 Aug 2020 12:16:24 GMT

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

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

Anonymous — Wed, 05 Aug 2020 12:54:34 GMT

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

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

RA_gov — Wed, 05 Aug 2020 12:49:40 GMT

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

amitchandak — Wed, 05 Aug 2020 12:50:40 GMT

RA_gov , Try a measure like

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

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

Greg_Deckler — Wed, 05 Aug 2020 12:53:45 GMT

whoops

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

RA_gov — Wed, 05 Aug 2020 13:38:22 GMT

Nope.

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

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

Anonymous — Wed, 05 Aug 2020 13:54:47 GMT

RA_gov

This should work

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

RA_gov — Wed, 05 Aug 2020 16:42:16 GMT

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.

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

Anonymous — Wed, 05 Aug 2020 20:56:04 GMT

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)

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

RA_gov — Fri, 07 Aug 2020 17:34:13 GMT

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