PowerBI - Row calculation for many to one table in a direct query

Question

I have the following two tables - App and App Role joined on 'ApplicationID'. There exists only one ApplicationID row in App table but multiple ApplicationIDS can exists in App Role table.

Application table:

Application ID | Tbed | New_Existing |

1234 | 5 | New

234| 10| Existing

App Role Table

ApplicationID | App Type | Role

1234| Individual| GC

1234| Org|GC

1234|Org| PB

1234|Individual|GC

234| Individual | PB

DAX'S I tried:

Level 1 = IF( 'App'[TBUS] <= 24 && (TRUE) 'App'[NE] = "Existing", (TRUE) IF( SUMX(FILTER('Application Role', 'App Role'[Role] = "PB"(TRUE) && 'App Role[App Type] = "Individual" (FALSE),1)>0, 1, 0 ), 0 )0 )

I have tried SUMX, MAXX, COUNTROWS. In the above example: the formula should return false for 1234 because App type = Individual (True) and the corresponding value of App Role= "PB" is False(0) but I get "1".

Any reviews or recommendations?

Answer 1

Thanks for PBIX. I assumed you had a relationship between the two tables.

If you do define a relationship on App ID column then you can use:

Level 1a = IF(
  ('App'[Total] <= 24) && ('App'[App Purpose] = "Purchase" || 'App'[App Purpose] ="Refinance"),
  IF(CALCULATE(COUNTROWS('App Role'), 'App Role'[Applicant Type]= "Individual" && 'App Role'[Role] = "PB") > 0  ,1,0),0)

Otherwise without the relationship, you could use:

Level 1b = 
  var meId = [App ID]
  var cRows = CALCULATE(COUNTROWS('App Role'), 'App Role'[App ID] = meId && 'App Role'[Applicant Type]= "Individual" && 'App Role'[Role] = "PB")
  var result = IF(('App'[Total] <= 24) && ('App'[App Purpose] = "Purchase" || 'App'[App Purpose] ="Refinance"), cRows)
  RETURN IF(result > 0, 1, 0)

Do split your logic like above, helps for readability.