cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Reply
NipawanV
Frequent Visitor

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

@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
Frequent Visitor

@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
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors