Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
@RA_gov , Try a measure like
calculate(distinctcount(Table[CUSTODIAN_NAME]),Table[CUSTODIAN_NAME] <>"CUSTODIAN")
Nope.
I Need: If BANK_NAME has CUSTODIAN_NAME = 'CUSTODIAN', count all the other custodians for that particular bank
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
)
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.
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)
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
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
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
@RA_gov -
Place Bank name and this measure in a visual:
Measure =
COUNTROWS(FILTER('Table',[CUSTODIAN_NAME] = 'CUSTODIAN'))
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
43 | |
22 | |
21 | |
16 | |
15 |
User | Count |
---|---|
46 | |
31 | |
29 | |
18 | |
18 |