topic Re: PowerBI- How to write a calculate column when two tables have many to one relationship? in DAX Commands and Tips

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

Khushboo9966 — Thu, 05 Oct 2023 17:00:17 GMT

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?

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

BeaBF — Thu, 05 Oct 2023 17:34:55 GMT

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

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

Khushboo9966 — Thu, 05 Oct 2023 18:12:54 GMT

BeaBF That worked! Thank you so much.

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

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

BeaBF — Thu, 05 Oct 2023 17:55:51 GMT

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

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

Khushboo9966 — Tue, 17 Oct 2023 14:06:24 GMT

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?

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

BeaBF — Tue, 17 Oct 2023 14:10:21 GMT

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

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

Khushboo9966 — Tue, 17 Oct 2023 14:43:58 GMT

BeaBF : Error: SUMX cannot work with type Boolean

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

BeaBF — Tue, 17 Oct 2023 14:48:31 GMT

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

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

Khushboo9966 — Tue, 17 Oct 2023 15:17:49 GMT

BeaBF The formula works but issue with False case.

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

BeaBF — Tue, 17 Oct 2023 15:28:20 GMT

Khushboo9966 could you provide more information?

BBF

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

Khushboo9966 — Tue, 17 Oct 2023 15:43:01 GMT

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.

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

Khushboo9966 — Tue, 17 Oct 2023 21:08:22 GMT

BeaBF : Any thoughts?

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

Khushboo9966 — Wed, 18 Oct 2023 22:00:07 GMT

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

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

BeaBF — Thu, 02 Nov 2023 09:48:34 GMT

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