PowerBI- How to write a calculate column when two tables have many to one relationship?

[Khushboo9966]

Hi, 

I have two table App(one relationship) and App Role(Many relationship). The direction of filteration is from App to App Role. This is a directquery model so RELATED, RELATEDTABLE are not supported functions. 

I have two columns(Role, Type) in App role table. I am writing a IF condition in App table something like below but I get only 0's. 

PrimaryB_Individual(A calculated column I created in App Role table): 

PrimaryB_Individual = IF(

    'App Role'[Role] = "PB" &&

    'App Role'[App Type]= "Individual",

    1, BLANK())

Level 1 is in App Table. This formula only gives me 0. 

Level 1 = IF(

    IF(not ISEMPTY( 'App'),

    SELECTEDVALUE('App Role'[PrimaryB_Individual])

) &&

    'App'[TBUS] <=24 &&

    'App'[NE] = "Existing",

        1,0

        )

 Any comments on what am I missing?

[Khushboo9966]

@BeaBF : Any thoughts?

[BeaBF · Accepted solution]

@Khushboo9966 Hi!

Try with:
Level 1 =
IF(
NOT ISEMPTY('App') && 
'App'[TBUS] <= 24 && 
'App'[NE] = "Existing", 
IF(
SUMX(FILTER('App Role', 'App Role'[Role] = "PB" && 'App Role'[App Type] = "Individual"), 1) > 0,
1,
0
),
0
)


BBF

[Khushboo9966]

@BeaBF That worked! Thank you so much. 

If you don't mind, What is wrong in my DAX? 

[BeaBF]

@Khushboo9966 sure.
In your original formula, you attempted to use SELECTEDVALUE to retrieve the value of 'PrimaryB_Individual' from the 'App Role' table. However, this usage of SELECTEDVALUE is problematic because it doesn't have a clear context for which 'App Role' row it should be considering. Additionally, your original formula didn't properly integrate the conditions from both the 'App' and 'App Role' tables.

The formula I provided in my previous response addresses these issues by using SUMX and FILTER functions to filter and count rows in the 'App Role' table based on the specified conditions from both tables. This ensures that the formula correctly links the two tables and returns the desired result based on the combined conditions.

I hope I was clear, BBF

[Khushboo9966]

Hi @BeaBF 

The formula works for all TRUE TRUE TRUE cases and False False False cases. I get an error when there is TRUE && TRUE && FALSE = FALSE(But I get TRUE). 

For example:  

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

The result should be 0 but I get 1. Any ideas?

[BeaBF]

@Khushboo9966 Hi!

Try with:

Level 1 =
IF(
NOT ISEMPTY('App') &&
'App'[TBUS] <= 24 &&
'App'[NE] = "Existing",
IF(
CALCULATE(SUMX('App Role', 'App Role'[Role] = "PB" && 'App Role'[App Type] = "Individual")) > 0,
1,
0
),
0
)

BBF

[Khushboo9966]

@BeaBF : Error: SUMX cannot work with type Boolean

[BeaBF]

@Khushboo9966 
Level 1 =
IF(
NOT ISEMPTY('App') &&
'App'[TBUS] <= 24 &&
'App'[NE] = "Existing",
IF(
COUNTROWS(FILTER('App Role', 'App Role'[Role] = "PB" && 'App Role'[App Type] = "Individual")) > 0,
1,
0
),
0
)

BBF

[Khushboo9966]

@BeaBF The formula works but issue with False case.  

[BeaBF]

@Khushboo9966  could you provide more information?

BBF

[Khushboo9966]

@BeaBF 

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

The result should be 0 but I get 1.

I am not sure if Countrows would work in this siutation because, the App Role has duplicate Application IDS(Primary Key joined with Application Table(Application ID)).

For example Application Role Table: 

Application ID	App Type	Role
1234	Individual	gc
1234	Org	GC
1234	Org	PB
1234	Individual	GC

So the formula should return false because  App type = Indivdiual (True) but the corresponding value of App type = "Individual" is False. 

I hope this makes sense. 

[BeaBF]

@Khushboo9966 Try with:
=
IF(
AND('App'[TBUS] <= 24, 'App'[NE] = "Existing"),
IF(
SUMX(
FILTER('App Role',
'App Role'[Role] = "PB" && 'App Role'[App Type] = "Individual"
),
1
) > 0,
1,
0
),
0
)

BBF

[Khushboo9966]

@BeaBF 

I have the sample data attached here 

Hi, I have attached a sample PBIX file, Refer to App ID 914, The result of Level 1 should be 0 but I get 1. 

https://github.com/khushs9966/SampleAnalysis/blob/main/test.pbix