Conditional Column where Matches Found across multiple columns and tables define the condition

[ihartdata]

Hello! 

I need help creating a conditional column that identifies whether an organization is a Partner or a Customer. I have a Feedback table where there is an Organization Name, and Organization ID and sometimes a PartnerID. Then I have a table of Partners that has two columns for the Partner Name, two columns for different PartnerIDs and another column for Organization ID. 

I'm looking to create a conditional column in the Feedback table that does the following: 

IF the EXACT OrganizationName, ignoring letter case, is found in the Partner table in the PartnerName OR in the PartnerFriendlyName, then OrgType is Partner

OR

IF the OrgID is found in the Partner table in the OrgID then the OrgType is Partner

OR

If the PartnerID is found in the Partner table in the PartnerID or the PartnerParentID then the OrgType is Partner,

Else, Customer

Link to example data -

https://1drv.ms/u/s!AvNGvMlvmRwehbliBtPENNDOLo0DKQ  

Partner Table

PartnerID	PartnerParentID	OrgID	PartnerName	PartnerFriendlyName
567890	55574835	12345	Adoxaceae	Moschatel
999532	65201234	25461	Altinglaceae	Sweetgum
102535	88923456	34512	Anacardiaceae	Cashew
420005	78905432	55123	Betulaceae	Birch
650010	42501234	67892	Araliaceae	Ivy

Feedback Table

FeedbackID	OrganizationName	OrgID	PartnerID	OrgType
1	Sambucus	68543	null
2	Adoxaceae	12345	55574835
3	SweetGum	25462	999532
4	Apocynaceae	34512	102535
5	Birch	55123	78905432
6	Holly	56432	null
7	Adoxacea, Ltd.	89234	null

Result of the query should be the OrgTypes listed based on the highlighted green values matching in the Partner table. 

Feedback Table

FeedbackID	OrganizationName	OrgID	PartnerID	OrgType
1	Sambucus	68543	null	Customer
2	Adoxaceae	12345	55574835	Partner
3	SweetGum	25462	999532	Partner
4	Apocynaceae	34512	102535	Partner
5	Birch	55123	78905432	Partner
6	Holly	56432	null	Customer
7	Adoxacea, Ltd.	89234	null	Customer

Please help! and thank you so much for your time and expertise. i<3data. 

[Anonymous]

Hi @ihartdata 

Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it. Or you are still confused about it, please provide me with more details about your problem or share me with your pbix file from your Onedrive for Business.

Best Regards,

Rico Zhou

[rcharara]

Add a column to the Feedback table :

OrgType =

var ornPartnerName =LOOKUPVALUE( Partner[PartnerName],Partner[PartnerName],Feedback[Organization Name])

var ornPartnerFriendly = LOOKUPVALUE( Partner[PartnerName],Partner[PartnerFriendlyName],Feedback[Organization Name])

var ornOrgID = LOOKUPVALUE(Partner[OrgID],Partner[OrgID],Feedback[OrgID])

var ornPartnerID =LOOKUPVALUE( Partner[PartnerID],Partner[PartnerID],Feedback[PartnerID])

var ornPartnerParentID = LOOKUPVALUE( Partner[PartnerID],Partner[PartnerParentID],Feedback[PartnerID])

var isPartner = ornPartnerName & ornPartnerFriendly & ornOrgID & ornPartnerID & ornPartnerParentID

RETURN

IF(isPartner <> "", "Partner", "Customer")

[ihartdata]

thank you @rcharara for the response. I think something is being lost in tranlation. 🙂  when I view the dax query it says 'var orn'. I can't seem to translate this to a dax statement. Can you tell me what is meant by this by sending me a link to the ormula or translate to enlish. I can't seem to get what the orn stands for. 😐

[Anonymous · Accepted solution]

Hi @ihartdata 

I build a calculated column to add OrgType in Feedback Table and use Conditional Formatting in table visual to show values in Partner Table in Green.

OrgType column:

OrgType = 
VAR _FBOrgName =
    Feedback[Organization Name]
        IN UNION (
            VALUES ( Partner[PartnerName] ),
            VALUES ( Partner[PartnerFriendlyName] )
        )
VAR _FBOrgID =
    Feedback[OrgID] IN VALUES ( Partner[OrgID] )
VAR _FBPartnerID =
    Feedback[PartnerID]
        IN UNION ( VALUES ( Partner[PartnerID] ), VALUES ( Partner[PartnerParentID] ) )
VAR _Condition = _FBOrgName || _FBOrgID
    || _FBPartnerID
RETURN
    IF ( _Condition = TRUE (), "Partner", "Customer" )

Build color measure to add in conditional formatting.

Color_OrgName = 
VAR _FBOrgName =
    MAX(Feedback[Organization Name])
        IN UNION (
            VALUES ( Partner[PartnerName] ),
            VALUES ( Partner[PartnerFriendlyName] )
        )
RETURN
    IF ( _FBOrgName = TRUE (), 1, 0 )

Color_OrgID = 
VAR _FBOrgID =
    MAX(Feedback[OrgID]) IN VALUES ( Partner[OrgID] )
RETURN
    IF ( _FBOrgID = TRUE (), 1, 0)

Color_PID = 
VAR _FBPartnerID =
    MAX(Feedback[PartnerID])
        IN UNION ( VALUES ( Partner[PartnerID] ), VALUES ( Partner[PartnerParentID] ) )
RETURN
    IF ( _FBPartnerID = TRUE (), 1, 0 )

Steps for OrgID: Select Table visual - Format - Conditional formatting - find OrgID - Turn on Fond color - Format by Rules and select measure in based on field as below.

Result:

For more info to conditional formatting function: Use conditional formatting in tables

You can download the pbix file from this link: File

Best Regards,

Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.