Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
RadhakrishnaE
Frequent Visitor

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.

 

1 ACCEPTED SOLUTION
Tahreem24
Super User
Super User

@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.
Capture.JPG
 
Don't forget to give thumbs up and accept this as a solution if it helped you!!!

Please take a quick glance at newly created dashboards : Restaurant Management Dashboard , HR Analytics Report , Hotel Management Report, Sales Analysis Report , Fortune 500 Companies Analysis , Revenue Tracking Dashboard

View solution in original post

4 REPLIES 4
Tahreem24
Super User
Super User

@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.
Capture.JPG
 
Don't forget to give thumbs up and accept this as a solution if it helped you!!!

Please take a quick glance at newly created dashboards : Restaurant Management Dashboard , HR Analytics Report , Hotel Management Report, Sales Analysis Report , Fortune 500 Companies Analysis , Revenue Tracking Dashboard

Thank you @Tahreem24  you gave me the exact answer.

selimovd
Super User
Super User

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
 
amitchandak
Super User
Super User

@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

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.