Creating a column using two separate columns from different query.

[Anonymous]

Hello,

I have been trying to figure out how I can create a column that are based off of he date from 2 seperate columns that come from 2 seperate querys.

Example 

 Query/table 1                              Query /table 2                    

ID #                                             ID # a2

inital test                                      final test 2

columm x                                     columm y

Desired result (this table is visualizaton table created from multiple Tables )

ID #                     inital test                           Final test                                Status 

123                      pass                                   "blank"                                 In-process

124                      "blank"                               "blank"                                 "blank"                                          

125                      pass                                     pass                                     Ready

I need to create column in a table that says 

If  ID number passes final test it is ready if ID passes inital but final is blank than in process. 

These two query are link to a third quarey by ID# so it did not alow me to creat a realtionship to each other.

Both Inital test and Final test are created based on other colmns with in their query/tables. 

Can someone help with this? 

[Anonymous · Accepted solution]

why don't you merge two tables based on id's.

Then create calculated column for status.

Thanks & regards,
Pravin Wattamwar
www.linkedin.com/in/pravin-p-wattamwar

If I resolve your problem Mark it as a solution and give kudos.

[az38]

Hi @Anonymous 

try a LOOKUPVALUE() function, like

Status = 
var _initialStatus = LOOKUPVALUE('Table1'[initial test], [ID #], [ID #])
var _finalStatus = LOOKUPVALUE('Table2'[final test], [ID #], [ID #])
RETURN
SWITCH(true(),
NOT(ISBLANK(_finalStatus)), "Ready",
NOT(ISBLANK(_initialStatus )) && ISBLANK(_finalStatus), "In-process",
BLANK()
)

[Anonymous]

When i attempted this i got an error code 

"A table of multiple values was supplied where a single value was expected."

When I creates a column using lookup it only  transfered data for ID that final had and notthat ones that inital bad but final didnt.

[az38]

@Anonymous 

have you got any duplicated [ID #] value in your tables?

[Anonymous]

@az38 

No none of the ID are Duplicated 

Inital ID	intial test	Fianl ID	Final test
123	P	123	P
124	P	124	P
125	P

New ID	status
123	ready
124	ready
125	inprocess

Here is my end goal. 

[Anonymous]

Hi @Anonymous 

Create new column 

Status=If(table[inittial test]="Pass" && table[final test]="Pass","Pass","Inprogress")

Thanks & regards,
Pravin Wattamwar
www.linkedin.com/in/pravin-p-wattamwar

If I resolve your problem Mark it as a solution and give kudos.

[Anonymous]

@Anonymous  yes this status code would work but since all the test data is not being transfered over to the merged column either 

i woudl only getht he test data for only id that the final table woudl have and none for any additional id intal testing would have. 

I have tried using the userelationship and it also did the same. 

Inital test = CALCULATETABLE(SUMMARIZE('Inital','Inital'[Pass/ Fail ]),USERELATIONSHIP('inital '[ID ],'C of C'[ID]) )

and this also did not transfer all the data.

[az38]

@Anonymous 

try to first create a new calculated table:

dimID = DISTINCT('table1'[ID #])

then create a column in this new table

Status = 
var _initialStatus = CALCULATE(MIN('Table1'[initial test]), 'Table1'[ID #]='dimID'[ID #])
var _finalStatus = CALCULATE(MAX('Table2'[final test]), 'Table2'[ID #]='dimID'[ID #])
RETURN
SWITCH(true(),
NOT(ISBLANK(_finalStatus)), "Ready",
NOT(ISBLANK(_initialStatus )) && ISBLANK(_finalStatus), "In-process",
BLANK()
)

[Anonymous · Accepted solution]

@az38 @Anonymous 

So Feeling really stupid but I was able to just merge the columns and make it work. Sorry it only my 3week uring power bi now and Thank you both for all teh help. 

[az38]

@Anonymous 

Im glad to hear!

So, write off here your solution and set it as Solution for future users

[Anonymous · Accepted solution]

why don't you merge two tables based on id's.

Then create calculated column for status.

Thanks & regards,
Pravin Wattamwar
www.linkedin.com/in/pravin-p-wattamwar

If I resolve your problem Mark it as a solution and give kudos.

[Anonymous]

@Anonymous 

So I tried merging them and had the same issue. where the data that is in the inital only transfer over test data for that of the final id and none that the final id didnt have.