Filter formulas

[rpmitchell]

Hello,

I have a question on how to construct a Formula. I have 4 tables. Each table has an Account number and an Amount. Table 1 is my "Data" table, and can have the same account number multiple times. The other 3 tables are "Lookup" tables, and will not have duplicate account numbers. I have created a relationship between my Data and all 3 lookup tables, using the account number. An account number may or may not be in each of the 4 tables.

I want several different scenarios.

I want to Sum the Amount in Table 4 only if it has a corresponding account number in Table 1 but not in Table 2 or 3.
I want to Sum the Amount in Table 4 only if it has a corresponding account number in Table 1 and 2, but not in 3.
I want to Sum the Amount in Table 4 only if it has a corresponding account number in Table 1 and 2 and 3.

I've tried various combinations of Calculate and Filter, but can't seem to get the pattern right. Can anyone give me some insight?

Thanks!

 

[iliace]

I would use the MATCH function to get this output. Let's say your Table 1 is in A:B, and the accounts are in $A$2:$A$100

=ISNUMBER(MATCH(12345, $A$2:$A$100, 0))

will give you TRUE if account 12345 exists. If your accounts are numbers stored as text, you will need a slight modification:

=ISNUMBER(MATCH(TEXT(12345, "00000"), $A$2:$A$100, 0))

Presumably, you will replace 12345 with a cell reference to your Table 4, let's say column J.

Let's say your Table 4 is in J:K, accounts being in J, and amounts in K. Your Table 2 is in D:E, and your Table 3 is in G:H. We are looking for this scenario: "I want to Sum the Amount in Table 4 only if it has a corresponding account number in Table 1 but not in Table 2 or 3." In column K, you would use this formula:

=IF(AND(ISNUMBER(MATCH(K2, $A$2:$A$100, 0)), NOT(ISNUMBER(MATCH(K2, $D$2:$D$100, 0))), NOT(ISNUMBER(MATCH(K2, $G$2:$G$100, 0)))), SUMIF($A$2:$A$100, J2, $B$2:$B$100), 0)

Notice I'm using AND to confirm existence/non-existence in multiple tables. To confirm non-existence, you have to add the NOT function to the ISNUMBER part. Blue determines whether the account J2 is in Table 1, yellow determines that account is not in Table 2, and Green establishes that it is not in Table 3. If all three conditions are met, the values in Table 1 are summed based on the account. If not, the output is zero.

Again, if your account numbers are stored as text replace J2 with TEXT(J2, "00000"), where 00000 is the number of digits/characters in your account number.

Hope this helps.

 

[scottsen]

It always makes me feel terrible when this happens, but...

iliace, this is the powerpivot forum. Your answer is traditional excel, not the hot new freshness

rpmitchell -- is this in calculated columns, or measures (calculated fields) ?

In a calc column, you can check the lookup full blank like, in the fact table. like...

= IF (ISBLANK(RELATED(LookupTable2[MyColumn])), "Not in Table 2")

For a measure... you are gonna have to be careful about the direction the filters "flow"... or do some wacky lookups, and I'll cry less if you give a sample workbook via google drive, one drive, dropbox, etc.

 

[iliace]

It always makes me feel terrible when this happens, but...

iliace, this is the powerpivot forum. Your answer is traditional excel, not the hot new freshness

Ooops! How did I end up here?

 

[rpmitchell]

Hi Scott,</SPAN>

I am actually building this in the 2013 Excel Data Model using power pivot.</SPAN>

I have the following 4 tables:</SPAN>

Table 1 is a "Contractual Adjustments" table. Named Adj.</SPAN>

Table 2 is a prior months ATB (Adjusted Trial Balance). NamedPMATB</SPAN>

Table 3 is a current months ATB. Named CMATB.</SPAN>

Table 4 is a current months Charges table. Named Chgs.</SPAN>

I built 2 helper columns in Table 1, to tell me whether or not the account</SPAN>

was is Table 2 or 3. I used the Related function for this. Then I built the following measure</SPAN>

in Table 4:</SPAN>

CALCULATE([EpicChgs],FILTER(Adj,Adj[PMATB]="Yes"),Filter(Adj,Adj[CMATB]="Yes"))</SPAN>

This seems to be working fine, by giving me only the charges on accounts that have a corresponding</SPAN>

account number in Tables 1, 2 and 3. However, I do have a question. Why do I have to use two Filter functions?</SPAN>

I tried using the & to combine the two criteria columns, but I kept getting an error message saying the they were</SPAN>

different data types, which doesn't make sense, since both calc columns are text that return either "Yes" or "No". </SPAN>

But it only worked when I separated them with two Filters.</SPAN>

By the way Scott, I read your "The Many Faces of Values" post on Rob's blog last week and loved. It. </SPAN>

I guess I was looking for a way to use Values or Filter to return a list of account numbers from any given table</SPAN>

and use that as a way of eliminating rows that I didn't want included in the current measure,whatever that might be.</SPAN>

I actually used the pattern above 4 times, alternating the Yes and No combinations, and it seems to work, but is there</SPAN>

a better or more efficient way?</SPAN>

<TBODY>
</TBODY><COLGROUP><COL></COLGROUP>

 

[scottsen]

For technical reasons, the formula you ended up using w/ 2 filters... it is likely FASTER than using a &, but let's ignore that

This is what you tried and it didn't work!?
=CALCULATE([EpicChgs], FILTER(Adj ,Adj[PMATB]="Yes" && Adj[CMATB]="Yes"))

It seems likely there is a better way, since your pattern does look kinda crazy Any chance you can share the workbook?

 

[rpmitchell]

For technical reasons, the formula you ended up using w/ 2 filters... it is likely FASTER than using a &, but let's ignore that

This is what you tried and it didn't work!?
=CALCULATE([EpicChgs], FILTER(Adj ,Adj[PMATB]="Yes" && Adj[CMATB]="Yes"))

It seems likely there is a better way, since your pattern does look kinda crazy Any chance you can share the workbook?

Oh, I only used 1 &, instead of &&. I'll have to go back and try that.
I'm afraid I can't share the workbook without some cleanup. There are actually many more columns in each table, but I only mentioned the relevant ones. I'm in Healthcare, and this is all patient level detail.

 

[scottsen]

One & is for string concat. Two && is the logical AND().

A picture of your model / relationships might be enough.

Man, and "obfuscate" feature would rule!