Forum Discussion

RadhakrishnaE's avatar
RadhakrishnaE
Frequent Visitor
4 years ago
Solved

Need DAX for below logic

Hi Team,

 

I need DAX for below logic.

I have two tables 1. invoice 2.policy 

Invoice table: 

Inv NoRolePolid
INV1ExecutivePol1
INV2ReprtesentativePol2

 

Policy Table:

PolidPrimary
Pol1Y
Pol2N

 

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.

 

polidinvnoRole
Pol1Inv1Exe - Primary
Pol2Inv2Rep- additional

 

Thanks in Advance.

 

Need Help
  • Tahreem24's avatar
    Tahreem24
    4 years ago

    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.
     

4 Replies

  • Tahreem24's avatar
    Tahreem24
    Super User
    4 years ago

    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.
     
  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    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

  • selimovd's avatar
    selimovd
    Most Valuable Professional
    4 years ago

    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