Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Adding conditional column from different tables and different criteria

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 ...
Need Help
AllisonKennedy's avatar
AllisonKennedy
Community Champion
5 years ago

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's avatar
Anonymous
Not applicable
5 years ago

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,