Forum Discussion

massotebernoull's avatar
4 years ago
Solved

Creating 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

ITEMOTHER data...
A 
B 
C 
D 
E 
F 
G 
H 
I 
J 

 

Table 2

ITEMSELLINGSTATUS
ABOOKACCEPTED
BTRYING CONTACTACCEPTED
CINVITEACCEPTED
AINVITEDENIED
ETRYING CONTACTIN COURSE
GTRYING CONTACTDENIED
GTRYING CONTACTACCEPTED
HBOOKIN COURSE
IBOOKIN COURSE
IBOOKDENIED

 

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:

ITEMSTATUSOTHER THINGS
AALREADY MADE CONTACT
BALREADY MADE CONTACT
CALREADY MADE CONTACT
DNO CONTACT
EALREADY MADE CONTACT
FNO CONTACT
GALREADY MADE CONTACT
HALREADY MADE CONTACT
IALREADY MADE CONTACT
JNO 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

  • 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.