Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Solved! Go to 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.
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
Add a column to the Feedback table :
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.
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.
User | Count |
---|---|
126 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
137 | |
116 | |
102 | |
71 | |
61 |