topic Re: How do I create DAX formula for this accounts hierarchy scenario ? in DAX Commands and Tips

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

NipawanV — Thu, 02 Jun 2022 05:41:19 GMT

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"

)

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

SpartaBI — Thu, 02 Jun 2022 07:59:46 GMT

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

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

NipawanV — Thu, 02 Jun 2022 08:14:59 GMT

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.

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

SpartaBI — Thu, 02 Jun 2022 08:44:26 GMT

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 🙂

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

NipawanV — Thu, 02 Jun 2022 12:10:58 GMT

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.

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

SpartaBI — Thu, 02 Jun 2022 12:16:37 GMT

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 🙂

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

NipawanV — Thu, 02 Jun 2022 12:44:03 GMT

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

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

NipawanV — Tue, 07 Jun 2022 09:35:11 GMT

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

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

SpartaBI — Tue, 07 Jun 2022 11:26:03 GMT

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