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

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
NipawanV
Helper I
Helper I

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

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: 

KA.PNG

 

 

 

 

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"
)
1 ACCEPTED SOLUTION
SpartaBI
Community Champion
Community Champion

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

View solution in original post

8 REPLIES 8
NipawanV
Helper I
Helper I

@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".  

@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
Helper I
Helper I

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

SpartaBI
Community Champion
Community Champion

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

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.

 

KA_if error message.PNG

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 🙂

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. 

@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 🙂

Helpful resources

Announcements
July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 community update carousel

Fabric Community Update - June 2025

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