cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
dgjacobson Frequent Visitor
Frequent Visitor

Order of logical operators or nesting of logical operators in a Calculate statement

Hello:

 

I created the following measure to sum account entries by filtering on department and account. It does not work. The Department filter needs to apply to all of the Accounts but in the way this is written, it does not. I can't use () inside of the CALCULATE statement to group the accounts. What is the most efficient way to accomplish this task. i could write individual expressions but I imagine there is a better way.

WagesBenefits = CALCULATE(sum(GenJournalDetail[AccountValue]),
    GenJournalDetail[Department]>="604",
    GenJournalDetail[Department]<="999",
    GenJournalDetail[Account]>="60350",
    GenJournalDetail[Account]<="60450" ||
    GenJournalDetail[Account]>="60160",
    GenJournalDetail[Account]<="60225"||
    GenJournalDetail[Account]="60500" ||
    GenJournalDetail[Account]="60550" ||
    GenJournalDetail[Account]="60575" ||
    GenJournalDetail[Account]="60600" ||
    GenJournalDetail[Account]="60625" ||
    GenJournalDetail[Account]="60650"
 )

Thank you!

 

 

 
 
 
1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Order of logical operators or nesting of logical operators in a Calculate statement

@dgjacobson I would recommned to add a calculated field wiith condition to return 1 or 0 (true or false) andt hen in your measure use that column to filter the accounts.

 

This way you will able to put this logic in other measures as well using calculated column instead of writing this whole filter condition in measure.






Did I answer your question? Mark my post as a solution.

Proud to be a Datanaut! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





View solution in original post

2 REPLIES 2
Super User
Super User

Re: Order of logical operators or nesting of logical operators in a Calculate statement

@dgjacobson I would recommned to add a calculated field wiith condition to return 1 or 0 (true or false) andt hen in your measure use that column to filter the accounts.

 

This way you will able to put this logic in other measures as well using calculated column instead of writing this whole filter condition in measure.






Did I answer your question? Mark my post as a solution.

Proud to be a Datanaut! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





View solution in original post

dgjacobson Frequent Visitor
Frequent Visitor

Re: Order of logical operators or nesting of logical operators in a Calculate statement

@parry2k: Thanks! Simple solution but it works! ... whish I'd thought of it 🙂

 

I modified it slightly by creating  a few columns with natural account groups. Then my initial condition begins with an account group, eiliminating the filtering. I can then add the additional account filters if necessary. After a lot of experimentation, I found that I can add one account set that is a range defined by a >= and <= and as many indvidual account filters as necessary. But, if I try and include two conditions with a range in the same expression, it does not work. Easy to work around that though.

 

Thanks again.

Helpful resources

Announcements
Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 67 members 989 guests
Please welcome our newest community members: