Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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):
Level 1 is in App Table. This formula only gives me 0.
Any comments on what am I missing?
Solved! Go to 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 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 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
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?
@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
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
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.
@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
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
User | Count |
---|---|
51 | |
38 | |
20 | |
14 | |
13 |
User | Count |
---|---|
96 | |
71 | |
29 | |
20 | |
13 |