Forum Discussion

ihartdata's avatar
ihartdata
Microsoft Employee
5 years ago
Solved

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...
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    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. 

rcharara's avatar
rcharara
Regular Visitor
5 years ago

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's avatar
ihartdata
Microsoft Employee
5 years ago

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

    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.