Need DAX for below logic

[RadhakrishnaE]

Hi Team,

I need DAX for below logic.

I have two tables 1. invoice 2.policy 

Invoice table: 

Inv No	Role	Polid
INV1	Executive	Pol1
INV2	Reprtesentative	Pol2

Policy Table:

Polid	Primary
Pol1	Y
Pol2	N

I am expecting below result

like :  If primary flag is “Y” for any rep/exec, show Rep – primary or Exec – primary and if flag is “N”, show Rep – additional or Exec – additional.

polid	invno	Role
Pol1	Inv1	Exe - Primary
Pol2	Inv2	Rep- additional

Thanks in Advance.

[Tahreem24 · Accepted solution]

@RadhakrishnaE  - Follow the below steps:

Step 1:  First create a relation between both thos tables using Policy ID.

Step 2: Then create a new calculated column like below:

New Role = IF(RELATED('Policy Table'[Primary])="Y",'Invoice Table'[Role] &" - Primary",'Invoice Table'[Role] &" - Additional")

Step 3: You'll get the output like below. Also attaching my PBIX file for your reference.

[RadhakrishnaE]

Thank you @Tahreem24  you gave me the exact answer.

[selimovd]

Hey @RadhakrishnaE ,

I guess the two tables are connected with a relationship. If that's the case, the following measure should do it:

RoleNew =
IF(
    MAX( Policy[Primary] ) = "Y",
    "Rep – primary",
    "Rep – additional"
)

Here I check if the Primary is "Y" and if yes return "primary", otherwise "additional".

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[amitchandak]

@RadhakrishnaE , a new column like

new column =
var _1 = maxx(filter(Table2, Table1[polid] = table2[polid]),Table2[primary])
Var _2 = if(_1 ="Y", "Primary", "Additional")
return
left([Role],3) & " " & _2