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.
Hi Kasper,
Hope you can help me with one problem, I have 2 unrelated tables, CaseID column in both tables, there are CaseIDs which are not in Table1 but are in Table 2 and vice versa, plus some of the case IDs in Table2 there are multiple times. What I need is to have a field in Table1 where I could see if given CaseID is also represented somewhere in Table2.
Thanks, Nikolas
HI @Nikolas ,
Sure use the lookupvalue function: http://technet.microsoft.com/en-us/library/gg492170.aspx
Hello Kasper.I did the same steps as you, and I have the same end result. Just wanetd to warn other readers of your blog to when copying / pasting the queries, remember to rewrite the quotes symbol because it does not copy properly formatted. I also had some trouble to convert the data type of column salary and pct text to decimal. My solution was to use the command Replace values to change dot (.) by comma (,).I am very excited about the new possibilities that the Data Explorer can provide. Very good your post (i am a sport fan, too).