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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
dgjacobson
New Member

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
parry2k
Super User
Super User

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



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

View solution in original post

2 REPLIES 2
parry2k
Super User
Super User

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



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

@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
March Fabric Community Update

Fabric Community Update - March 2024

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

Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.