PowerPivot case: Does a value exist in both columns ?

I had a interesting question on my ask a questions page that I wanted to answer by solving it using PowerPivot.

The following question was asked:

If i have a powerpivot table whith phone numbers in two different columns. Can i make some kind if a search formula if i want to search a specifik number in each column or if the number exits in one or both columns?

Ok lets solve it. I have the following table in PowerPivot:

Now I want to create a list of numbers that I want to check. I create a table in Excel that contain the numbers that I want to check and click on the “Add to data model” button to add those numbers to the model:

Next I create a Pivottable:

Now the interesting thing is .. where is the other table that we just added? Excel has a new way to show tables, it looks at the pivottable context and determines what is used. You can turn that off by pressing “All” in the fieldlist. That will  show all tables. Now lets get a list of the numbers we just added to the model in the pivottable (you see the table that I used to add the numbers below the pivottable):

Now time for some DAX, we are going count the number of occurrences of the number from the table we added in both column of data. I add two measures instead of putting everything into one big measure for manageability and a third one to combine them:

CountRowA:=if(HASONEVALUE(Table2[Numbers]), CALCULATE(COUNTROWS(Table1),Table1[A]=VALUES(Table2[Numbers])))
CountRowB:=if(HASONEVALUE(Table2[Numbers]), CALCULATE(COUNTROWS(Table1),Table1[B]=VALUES(Table2[Numbers])))

CountRows:=[CountRowA] + [CountRowB]

This measure will calculate the number of rows for Table1 where the column A (or B) are the same as the value for Table2[Numbers] in the pivottable. The hasonevalue will make sure it will only compare the values if there is only one value for  Table2[Numbers], if there are more then one value (like on the Grandtotals row) the comparison will not work.

Here is the result:


Pretty easy right :). Now you could add values to the numbers table and these will automatically get aded to the pivottable and the number of occurrences will be automatically count.