topic Re: DAX Calculated measure verification in DAX Commands and Tips

DAX Calculated measure verification

Khushboo9966 — Tue, 17 Oct 2023 22:52:52 GMT

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,

)

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?

Re: DAX Calculated measure verification

DallasBaba — Wed, 18 Oct 2023 02:05:58 GMT

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

Re: DAX Calculated measure verification

Khushboo9966 — Wed, 18 Oct 2023 01:46:01 GMT

DallasBaba

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.

Re: DAX Calculated measure verification

DallasBaba — Wed, 18 Oct 2023 02:00:01 GMT

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!

Re: DAX Calculated measure verification

Khushboo9966 — Wed, 18 Oct 2023 18:29:04 GMT

DallasBaba Already tried, Did not work in the False case.

Re: DAX Calculated measure verification

DallasBaba — Wed, 18 Oct 2023 18:49:20 GMT

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!!!

Re: DAX Calculated measure verification

Khushboo9966 — Wed, 18 Oct 2023 19:41:36 GMT

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."

Re: DAX Calculated measure verification

DallasBaba — Wed, 18 Oct 2023 20:40:55 GMT

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

Re: DAX Calculated measure verification

Khushboo9966 — Wed, 18 Oct 2023 21:58:50 GMT

DallasBaba

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: DAX Calculated measure verification

DallasBaba — Thu, 19 Oct 2023 04:47:18 GMT

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.

Re: DAX Calculated measure verification

Anonymous — Thu, 19 Oct 2023 06:51:36 GMT

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.

Re: DAX Calculated measure verification

Khushboo9966 — Thu, 19 Oct 2023 14:55:40 GMT

Anonymous 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)

Re: DAX Calculated measure verification

Khushboo9966 — Thu, 19 Oct 2023 15:00:22 GMT

DallasBaba

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)

Re: DAX Calculated measure verification

DallasBaba — Sat, 21 Oct 2023 02:45:20 GMT

Anonymous

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

Re: DAX Calculated measure verification

Khushboo9966 — Mon, 23 Oct 2023 17:21:21 GMT

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.

Re: DAX Calculated measure verification

DallasBaba — Mon, 23 Oct 2023 17:57:17 GMT

Khushboo9966 Good to know you have it sloved. Your logic return the same values as Anonymous query.

Please give kudos to our effort by clicking the Thumbs Up!

Thanks