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.
The following is the Calculated column DAX I am using.
a) App Role is joined to App in a many-to-one relationship.
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
)
The result should be 0 but I get 1.
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 |
234 | Individual | PB |
234 | Org | GC |
So the formula should return false for 1234 because App type = Indivdiual (True) and the corresponding value of App type = "Individual" is False.
Where am I going wrong?
Solved! Go to Solution.
I cannot merge tables since this is direct query.
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)
The below formula did the trick for me.
@Khushboo9966 Good to know you have it sloved. Your logic return the same values as @v-rzhou-msft query.
Please give kudos to our effort by clicking the Thumbs Up!
Thanks
@Khushboo9966you can merge both tables as a flat table to create a measure to return 1 when conditions are met; else, 0? Get solution .pbix file on GitHub
https://github.com/DallasBaba/DaxSolutions/blob/main/Khushboo9966%20Solution.pbix
Solution =
VAR Case1 = IF(OR('OneTable'[App Purpose] = "Purchase", 'OneTable'[App Purpose] = "Refinance"), 1, 0)
VAR Case2 = IF(AND('OneTable'[Applicant Type] = "Individual",'OneTable'[Role] = "PB"), 1, 0)
RETURN
IF(
'OneTable'[Total] <= 24 &&
Case1 = 1 &&
Case2 = 1,
1,
0
)
I tried using the same measure to the values you wanted when the table was separated with a one-many relationship, but I had no luck until after merging the tables.
Kindly let me know if this meet you needs.
Thanks
Dallas
I cannot merge tables since this is direct query.
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)
The below formula did the trick for me.
@Khushboo9966 I am looking looking at the pbix file and I would like to mention, changing the || pipe opratorator to && will make Level1 result return 0.
Level 1 =
IF(
('App'[Total] <= 24) && // <-- Is this value base on a dynamic selecting or Always be lessthan 24?
('App'[App Purpose] = "Purchase" ||
'App'[App Purpose] ="Refinance"), // Do you want result to return 0 when AppPurpose = Purchase or Refinance ?
IF(MAXX(FILTER('App Role','App Role'[Applicant Type]= "Individual" && 'App Role'[Role] = "PB"),1) >0 ,1,0),0)
Please read the comments in the code line.
I need a clear understanding of what you need in return.
I have update my pbix with table relationships.
SampleAnalysis/test.pbix at main · khushs9966/SampleAnalysis (github.com)
I want to return True(1) when all are True else False(0). If there is True and False it should False(0) but my code return True(1).
Level 1 =
IF(
('App'[Total] <= 24) && // <-- Is this value base on a dynamic selecting or Always be lessthan 24? //(Yes, the formula will check each row
('App'[App Purpose] = "Purchase" ||
'App'[App Purpose] ="Refinance"), // Do you want result to return 0 when AppPurpose = Purchase or Refinance ? (No, I want them to return 1 when App purpose matches the critiea)
IF(MAXX(FILTER('App Role','App Role'[Applicant Type]= "Individual" && 'App Role'[Role] = "PB"),1) >0 ,1,0),0)
@Khushboo9966 let use CALCULATE function
Level 1 =
IF(
'App'[TBUS] <= 24 &&
'App'[NE] = "Existing",
IF(
CALCULATE(
COUNTROWS('Application Role'),
'App Role'[Role] = "PB" &&
'App Role'[App Type] = "Individual"
) > 0,
1,
0
),
0
)
The CALCULATE function filters the 'Application Role' table based on the specified conditions and counts the rows that meet those conditions. If the count is greater than 0, it returns 1; otherwise, it returns 0.
Let me know if this work
@ me in replies, or I'll lose your thread!!!
@Khushboo9966 your can modify the DAX formula
Level 1 =
IF(
'App'[TBUS] <= 24 &&
'App'[NE] = "Existing",
IF(
COUNTROWS(
FILTER(
'Application Role',
'App Role'[Role] = "PB" &&
'App Role'[App Type] = "Individual"
)
) > 0,
1,
0
),
0
)
Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!
@DallasBaba : No it doesn't work. I get the below error:
"This expression refers to a Column object named 'Application[Test]', which has an error."
@Khushboo9966 Do you have a table name 'Application'
Or are you trying to refere to 'Application Role' [Test]' before getting the error message ?
More also, can you share a pbix file of your work sample
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
Hi @Khushboo9966 ,
According to your statement, I think you can try code as below to update [Level1] calculated column.
Level 1 NEW =
VAR _PBLIST =
CALCULATETABLE (
VALUES ( 'App Role'[Role] ),
FILTER (
'App Role',
'App Role'[App ID] = EARLIER ( App[App ID] )
&& 'App Role'[Applicant Type] = "Individual"
)
)
RETURN
IF (
( 'App'[Total] <= 24 )
&& ( 'App'[App Purpose] = "Purchase"
|| 'App'[App Purpose] = "Refinance" ),
IF ( MAXX ( FILTER ( 'App Role', "PB" IN _PBLIST ), 1 ) > 0, 1, 0 ),
0
)
Result for you sample is as below.
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@v-rzhou-msft Formula runs an error in a circular redundancy. I have update my pbix file with table relationships. Can you please look into it?
SampleAnalysis/test.pbix at main · khushs9966/SampleAnalysis (github.com)
IF(
MAXX(
FILTER(
'Application Role',
'App Role'[Role] = "PB" &&
'App Role'[App Type] = "Individual"
),
1
) > 0,
This part still evaluated to true(1) instead of False(0). SO the final answer has to be 0 instead of 1.
@Khushboo9966 you can rewrite your calculated column DAX as:
Level 1 =
IF(
'App'[TBUS] <= 24 &&
'App'[NE] = "Existing",
IF(
MAXX(
FILTER(
'Application Role',
'App Role'[Role] = "PB" &&
'App Role'[App Type] = "Individual"
),
1
) > 0,
1,
0
),
0
)
This way, the MAXX function will return the maximum value of 1 in the filtered table for each ‘Application ID’, and if there is no row that meets the condition, it will return BLANK. This will ensure that your calculated column DAX returns 0 or 1 for each ‘Application ID’, as expected.
Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!
Let me know if this work
@ me in replies or I'll lose your thread!!!
Thanks
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 |
---|---|
44 | |
28 | |
22 | |
12 | |
8 |
User | Count |
---|---|
74 | |
51 | |
45 | |
16 | |
12 |