How do I create DAX formula for this accounts hierarchy scenario ?

[NipawanV]

Hi, I newly user for Power BI, always get stuck with the DAX formula.  This is the new requirement that I could not solved for my daily work.   I have the requirement to mark thousand of the account records as "TRUE" on Company and Site if the Account Level = "Group" and Key Account = "TRUE" as well the Group leve should also "TRUE" in the the condition field.  I don't want to change the existing data from D365 so try to create a new condition column called  "KAM" in column F. See my scenario case below on the excel: 

I tried to create the column formula "KAM" by using IF, SWITCH condition but couldn't get out the correct result.  Showing error messages that I could not solved.  Could you as the experts guide me how to build the formula ?  Many thanks.

These are my formulas that I got error message and never success.

1) IF statement 
 

KAM =

IF (

AND ( [Key Account] = "True", [Afry_accountlevel] = "Group"),

[Key Account] = "False" && [Afry_accountlevel] = "Company";

"True",

IF (

AND ( [Key Account] = "True", [Afry_accountlevel] = "Group"),

[Key Account] = "False" && [Afry_accountlevel] = "Site";

"True", "False"

)

)

2. SWITCH

KAM =

SWITCH(

TRUE();

[Key Account] = "True" && [Afry_accountlevel] = "Group"; "True";

[Key Account] = "False" && [Afry_accountlevel] = "Company"; "True";

[Key Account] = "False" && [Afry_accountlevel] = "Site"; "True";

"False"

)

[NipawanV]

@SpartaBI Hello, May I get your advice with the formula again.  I could not manage to get the correct result with the formula that I created.  As you can see from my excel table I need the "KAM" field to be "True" for all Accounts in the hierarchy (3 levels - Group | Compay | Site) by checking this condition => Account level is "Group" && Key Account is "True".  All child accounts must be "True".  

[SpartaBI]

@NipawanV hey, can you share with me the sample excel table and write there the logic you need and also add the dsired values manually in a column so I'll be sure what is the result you want and I'll reply with the DAX needed

[NipawanV]

@SpartaBI Many thanks indeed.  I will for sure if I get stuck. 😊

[SpartaBI · Accepted solution]

@NipawanV what is the error message you are getting?
Maybe you need to remove the "" from the true and false?

[NipawanV]

hi, I tried to remove "" from True / False as well as change ; to , where i make a blue tick.  This is the same error that I found.

[SpartaBI]

I actually looked at your switch version. Try there.
In the if version you probably have an extra / missing comma / brackets. Leave it for now 🙂

[NipawanV]

Many thanks.  Well actually it works but it given the wrong result.  Probably I miss one checking condition.  I mean KAM field should give True when all the accounts in Company and Site level has the same top parent account (Group).  I need to figure out what is the next formula to get the top account first. 

[SpartaBI]

@NipawanV my pleasure 🙂
If you don't succeed reply here and mention me and I will help you.

P.S.
Check out my showcase report:
https://community.powerbi.com/t5/Data-Stories-Gallery/SpartaBI-Feat-Contoso-100K/td-p/2449543
Give it a thumbs up if you liked it 🙂