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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ihartdata
Employee
Employee

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

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

PartnerIDPartnerParentIDOrgIDPartnerNamePartnerFriendlyName
5678905557483512345AdoxaceaeMoschatel
9995326520123425461AltinglaceaeSweetgum
1025358892345634512AnacardiaceaeCashew
4200057890543255123BetulaceaeBirch
6500104250123467892AraliaceaeIvy

 

Feedback Table

FeedbackIDOrganizationNameOrgIDPartnerIDOrgType
1Sambucus68543null 
2Adoxaceae1234555574835 
3SweetGum25462999532 
4Apocynaceae34512102535 
5Birch5512378905432 
6Holly56432null 
7Adoxacea, Ltd.89234null 

 

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

Feedback Table

FeedbackIDOrganizationNameOrgIDPartnerIDOrgType
1Sambucus68543nullCustomer
2Adoxaceae1234555574835Partner
3SweetGum25462999532Partner
4Apocynaceae34512102535Partner
5Birch5512378905432Partner
6Holly56432nullCustomer
7Adoxacea, Ltd.89234nullCustomer

 

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

1 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.

1.png

Result:

2.png

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. 

View solution in original post

4 REPLIES 4
v-rzhou-msft
Community Support
Community Support

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
Regular Visitor

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")
 
Conditional Column.png
  

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. 😐

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.

1.png

Result:

2.png

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. 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.