Adding conditional column from different tables and different criteria

[Anonymous]

Hello All, 

I am quite new to Power BI. This is going to be my first post to PBI community. I am trying to create a new column which name is history_funnel. After creating this column, I will be using lead_id and able to capture leads based on desired conditions.

I tried to create this column by custom column and conditional column but did not able to create below conditions in my query.

Any help much appreciated,

IF SFDC[lead].ownerid ' AND SFDC[leadhistory]New Value = 'Global Marketing' THEN  "MQL"
IF SFDC[lead]ownerid AND SFDC[leadhistory]New Value <> 'Global Marketing' THEN AL
If SFDC[lead]ownerid  AND SFDC[leadhistory]New Value CONTAINS 'Qualification' OR SFDC[LEAD].OwnerId ANDSFDC[leadhistory]. NEW VALUE = owner.Profile = 'Marketing Administrator' THEN QAL
If SFDC[lead]ownerid AND SFDC[leadhistory]New Value STARTS WITH "005" THEN SAL
IFSFDC[lead] status' AND SFDC[leadhistory]New Value = 'Converted to New Opportunity' THEN SQL
If SFDC[lead]status AND SFDC[leadhistory]NEw Value = 'Converted to Contact' OR 'Converted to Exisitng Opportunity' THEN SQC

Ownerid column is located in [SFDC] Lead Table

New Value is Located in [SFDC] Lead History Table( there is a relationship between lead and lead history table) lead.id=leadhistory.leadid

Global marketing, converted to new opportunity,converted to existing opportunity, qualification are rows of the status column from lead table.

Regards,

[Icey]

Hi @Anonymous ,

Please share us some sample data, not real data, and the result you expect.

Best Regards,

Icey

[AllisonKennedy]

@Anonymous The new column will need to be created in the Lead History table (the many side of the relationship). You also need to do this NOT in Power Query. DAX is done in the data view in the report. https://docs.microsoft.com/en-us/power-bi/transform-model/desktop-tutorial-create-calculated-columns

Your screenshots are of Power Query Editor, which uses M code (not DAX). So many windows and so many languages, it's hard to keep them all straight!

[AllisonKennedy]

@Anonymous Which way does the relationship flow and which table are you trying to create the column in? You cannot use the relationships in Power Query. See if my post here helps explain why: Power BI Order of Operations 

So, if you want to use condition from multiple tables and use the relationships, you will need to use DAX. You can try a New Column from Table tools tab in ribbon in report data view: 

Column = SWITCH(TRUE()

, SFDClead[ownerid] =  "Global Marketing" &&  SFDCleadhistory[NewValue] = "Global Marketing" , "MQL"

, SFDClead[ownerid] <>"Global Marketing" &&  SFDCleadhistory[NewValue] <>"Global Marketing", "AL" 
, CONTAINSSTRING(SFDClead[ownerid] , "Qualification") && CONTAINSSTRING( SFDCleadhistory[NewValue], "Qualification"), "QAL"

, SFDClead[ownerid] = "Marketing Administrator" && SFDCleadhistory[NewValue] = "Marketing Administrator", "QAL"

, LEFT(SFDClead[ownerid], 3) = "005" && LEFT(SFDCleadhistory[NewValue]) = "005", "SAL"

)

Continue this pattern to add in the below conditions as well (I have not finished as you will need to change the syntax to match your column names, and you may need to use the RELATED() function to access the column from the other table). This column must be created in the fact table, or the many side of the relationship. 
IFSFDC[lead] status' AND SFDC[leadhistory]New Value = 'Converted to New Opportunity' THEN SQL
If SFDC[lead]status AND SFDC[leadhistory]NEw Value = 'Converted to Contact' OR 'Converted to Exisitng Opportunity' THEN SQC

[Anonymous]

Thanks @AllisonKennedy  for the detailed explanaitons.

It doesn't quite work above query. I want to share little bit more details regarding data model.

First of all, below is the relationship between leadhistory and lead table.

lead.id=leadhistory.leadid

I am not able to see new column when I query the dax formula you indicated above. NewColumns is a conditional column. Is that reason why?

I was thinking creating this calculated conditional column in Lead table but maybe I am wrong. 

Do you have any suggestion which table should I use for this new column?

On the other hand, I have a field table in lead history table. I think below conditional column will be equal to first one but I am not able to use your DAX formula for my prior conditional column because NewValue does not pop up while I am querying.

IF  sfdcleadhistory.FIeld = 'Owner' AND sfdcleadhistory.New Value = 'Global Marketing'T (this definition in lead table and it is conditional column again) THEN  "MQL"
IF sfdcleadhistory.field = 'Owner' AND sfdcleadhistory.New Value <> 'Global Marketing'(this definition in lead table and it is conditional column again) THEN AL
If sfdcleadhistory.field = 'Owner' AND sfdcleadhistory.New Value CONTAINS 'Qualification'(this definition in lead table and it is conditional column again) OR field = 'Owner' AND NEW VALUE = owner.Profile = 'Marketing Administrator' THEN QAL
Ifsfdcleadhistory. field = 'Owner' AND sfdcleadhistory.New Value STARTS WITH "005" THEN SAL
IF sfdcleadhistory.field = 'Status' AND sfdcleadhistory.New Value = 'Converted to New Opportunity' THEN SQL
If sfdcleadhistory.field = 'Status' AND sfdcleadhistory.NEw Value = 'Converted to Contact'  OR 'COnverted to Exisitng Opportunity' THEN Mapping = SQC

Should I use lead or leadhistory table for this new column ? Is there any way to see new value while implementing your dax formula. Do I need to do any changes in data modeling? Should I use first or second conditional column?

Regards,