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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
EvanBeiser
Frequent Visitor

DAX : Filtering for multiple accounts

Hello all,

 

I am having some trouble with DAX when I attempt to filter for multiple accounts. Currently what I am using is,

 

Direct Labor = SUMX(FILTER('ACCOUNT_BALANCE',' ACCOUNT_BALANCE'[ACCOUNT]="00010"),[AMOUNT]) 

 

so as it shows, I am filtering to the Account row of the Account Balance table and then finding the sum of all amounts that are associated with account number "00010".  This works great when there is only one account number, but soon I'll need to find the sum for multiple account numbers. I have created multiple SUMX measures and then created another measure to sum those, but I figured there has to be an easier way or maybe I am just missing something when I attempt to filter for multiple account numbers.

 

Hopefully my explanation has been clear, and thank you in advance for any help.

 

Evan 

5 REPLIES 5
jeffreykeryk
Helper I
Helper I

I believe I have the same problem. I used DAX to replicate the Excel LINEST() slope function. Average Sals Bookings data is broken down by Tenure and Region. Tenure is the X-Axis (known); Average Bookings is the Y-Axis (variable). The DAX works fine, except it returns the slope value for all data, regardless of Slicer (by Region).

In the past, DAX measures work perfectly.

Sean
Community Champion
Community Champion

@EvanBeiser How are you going to Visualize this infromation?

 

If you create a Table with Account and Amount and use an Account Slicer - don't you get what you want?

 

Then you don't really need a Measure (especially if you have many accounts)

 

Not sure what you are trying to achieve?

That is currently what we have, but the issue with this is that when we go to create visuals for all of this financial information we can not filter for multiple accounts easily.  All of the accounting information is being pulled in from one general ledger.

 

As an example for visuals, we are using the LinearGauge to show gross margin as a percent of revenue by placing a gross margin measure in the value field and revenue measure in the target value field. I am not sure if there is a way to correctly filter for these two sepereate amounts when the information is coming from one table.

 

The other issue with creating another table is that we are dealing with a lot of data.Some have mentioned to avoid creating many additional tables because of out data set size.

Sean
Community Champion
Community Champion

@EvanBeiser Create a Table Visualization as in Picture. All accounts on Left - 2 accounts selected on Right.

This works the same way regardless of how many accounts you have.

Regarding the Gross Margin - it should work the same way.

What formula are you using?

Account Balance.png

wonga
Continued Contributor
Continued Contributor

Couldn't you just use an attribute slicer custom visual to sort through multiple account numbers? (This has a search bar, but it isn't fully developed if you have a long list of values; need to scroll through the entire list first before you can search the entire thing).

 

Also, the standard summation of the values in [AMOUNT] should do the job for you instead of going through the trouble of making DAX expressions for each account number. I might be misunderstanding your problem, so if you could provide a sample dataset and the expected value for "Direct Labor", that would probably clear a few things up. Thanks.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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