[Tad ElamParticipant Started: 1Replies: 5Total: 6]

Thanks Tom – I sent the email as you requested.

[Tad ElamParticipant Started: 1Replies: 5Total: 6]

Oh by the way – I registered for the anytime course.  I will be viewing that in my spare time.  I already have the first DAX Formulas for PP book by Rob, so the 2nd Edition will be a nice addition to my collection 🙂

[Tad ElamParticipant Started: 1Replies: 5Total: 6]

Yes the Accounts and Sales tables are related by Account ID.  I also verified the Customer field on the pivot is coming from the Accounts table.

Is it possible to use the RANKX(FILTER(xxx…. formula without using ALL()?  Every different way I try this formula and it just seems to hang on Reading Data…  I have waited 5+min and it doesn’t finish, I have to Esc to Cancel.

RANKX(FILTER(Account,[Key Account Rank]>15),[Growth Account Calc])

Furthermore:  I am using column filters – filtering out some Customers (b/c they aren’t actually customers), so my understanding is using ALL removes that filter.  Could that be having some effect on the formula?  I’m just throwing everything in the pot now, I’m thoroughly confused.

I have attached yet another screen shot – this time I removed all filters, and using my original formula of:

RANKX(FILTER(ALL(Account[Customer]),[Key Account Rank]>10),[Growth Account Calc])

I’m seeing duplicate ranks, just like before – even though the GROWTH_ACCOUNT_CALC numbers are not equal…  confused… and a little frustrated at this point. :(…   the FILTER isn’t working (because Key 7 is there, and the RANKX isn’t working (because there are dup’s when there shouldn’t be dup’s).

 

Attachments:

You must be logged in to view attached files.

[Tad ElamParticipant Started: 1Replies: 5Total: 6]

I appreciate the support very much, thanks.  I have sent the Live training info to my boss, hopefully he will allow me to attend the session in Indiana this spring.  I am also looking at the course you mentioned.

changing it to 15 did make it change, but did not correct the issue (see screen shot).  The Key Ranks of 2, 5 and 6 are still there 🙁

the way I read the formula: RANKX(FILTER(ALL(Account[Customer]),[Key Account Rank]>15),[Growth Account Calc]) in English would be

FILTER: grab all accounts with Key Account Rank > 15 and then RANKX: rank them by Growth Account Calc.

I don’t see any other way to read that, do you?  It’s just not filtering out the Key Account Ranks like we’re telling it to…  Also I’ve removed the TOPN call, which means it would take whatever (10 or 15) that I’m passing, but that yields the same results.  I’m completely baffled by this one.  Can you think of anything else I can try?

Thanks,

Tad

Attachments:

You must be logged in to view attached files.

[Tad ElamParticipant Started: 1Replies: 5Total: 6]

Hi Tom – thank you for the reply.

Currently there are no slicers involved.  That being said, the pivot is summarizing multiple “Ship-To” accounts (Each Bill-To account can have multiple Ship-To’s).  This means I can’t put a static calculated column in, as they would all be ranked 1, right?

This is my first real foray into DAX, so I’m a little baffled by the structuring.  I’m more used to building SQL tables with exactly what I need and then using that.  I do see the HUGE value though, in using DAX, so I’m excited to get more familiar with it.

I have attached another screenshot to try and explain better.  I wonder if you would consider a call and a screen sharing session so I could show you exactly what I’m dealing with?

Thanks in advance ,

Tad

Attachments:

You must be logged in to view attached files.

[Author]

Posts