DAX Calculated measure verification

[Khushboo9966]

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?

[DallasBaba]

@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

[DallasBaba]

@v-rzhou-msft 

@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

[Khushboo9966 · Accepted 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.  

[DallasBaba]

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

[Khushboo9966]

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

[DallasBaba]

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

[DallasBaba]

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

[Khushboo9966]

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

[DallasBaba]

@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

[Khushboo9966]

@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 

[v-rzhou-msft]

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.

[Khushboo9966]

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

[Khushboo9966]

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

[Khushboo9966]

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

[DallasBaba]

@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