Forum Discussion
massotebernoull
Helper I
4 years agoCreating a column with two tables (one with repeated values)
Hello guys,
I Have two tables, Table 1 with main data (dimension table), and Table 2 with repeated values (fact):
Table 1
| ITEM | OTHER data... |
| A | |
| B | |
| C | |
| D | |
| E | |
| F | |
| G | |
| H | |
| I | |
| J |
Table 2
| ITEM | SELLING | STATUS |
| A | BOOK | ACCEPTED |
| B | TRYING CONTACT | ACCEPTED |
| C | INVITE | ACCEPTED |
| A | INVITE | DENIED |
| E | TRYING CONTACT | IN COURSE |
| G | TRYING CONTACT | DENIED |
| G | TRYING CONTACT | ACCEPTED |
| H | BOOK | IN COURSE |
| I | BOOK | IN COURSE |
| I | BOOK | DENIED |
I'm trying to create a new column in Table 1 getting infortmations of Table 2 like this:
- If there are any informations about an item, then "Already made contact".
- If there isn't any information, I would like to get "No contact".
I tried to use dax to create a column like this:
IF(LOOKUPVALUE(Table2[STATUS], Table2[ITEM], Table1[ITEM]) = "", "No contact", "Already made contact")
but I get an error saying "A table of multiple values was supplied where a single value was expected".
The table 1 would like to get is like:
| ITEM | STATUS | OTHER THINGS |
| A | ALREADY MADE CONTACT | |
| B | ALREADY MADE CONTACT | |
| C | ALREADY MADE CONTACT | |
| D | NO CONTACT | |
| E | ALREADY MADE CONTACT | |
| F | NO CONTACT | |
| G | ALREADY MADE CONTACT | |
| H | ALREADY MADE CONTACT | |
| I | ALREADY MADE CONTACT | |
| J | NO CONTACT |
Is there another way to get this?
Hi,
This calculated column formula works
=if(CALCULATE(COUNTROWS(Table2),FILTER(Table2,Table2[ITEM]=EARLIER(Table1[ITEM])))>=1,"Already made contact","No contact")Hope this helps.
1 Reply
- Ashish_Mathur
Super User
Hi,
This calculated column formula works
=if(CALCULATE(COUNTROWS(Table2),FILTER(Table2,Table2[ITEM]=EARLIER(Table1[ITEM])))>=1,"Already made contact","No contact")Hope this helps.