topic Re: Creating a column using two separate columns from different query. in DAX Commands and Tips

Creating a column using two separate columns from different query.

Anonymous — Thu, 13 Feb 2020 16:19:28 GMT

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?

Re: Creating a column using two separate columns from different query.

az38 — Sun, 16 Feb 2020 19:35:34 GMT

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() )

Re: Creating a column using two separate columns from different query.

Anonymous — Tue, 18 Feb 2020 21:07:45 GMT

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.

Re: Creating a column using two separate columns from different query.

az38 — Tue, 18 Feb 2020 21:11:40 GMT

Anonymous

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

Re: Creating a column using two separate columns from different query.

Anonymous — Wed, 19 Feb 2020 07:57:35 GMT

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.

Re: Creating a column using two separate columns from different query.

Anonymous — Wed, 19 Feb 2020 13:09:56 GMT

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.

Re: Creating a column using two separate columns from different query.

Anonymous — Wed, 19 Feb 2020 13:19:09 GMT

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.

Re: Creating a column using two separate columns from different query.

Anonymous — Wed, 19 Feb 2020 13:25:23 GMT

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.

Re: Creating a column using two separate columns from different query.

Anonymous — Wed, 19 Feb 2020 13:40:12 GMT

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.

Re: Creating a column using two separate columns from different query.

az38 — Wed, 19 Feb 2020 17:08:48 GMT

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() )

Re: Creating a column using two separate columns from different query.

Anonymous — Mon, 24 Feb 2020 15:07:33 GMT

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.

Re: Creating a column using two separate columns from different query.

az38 — Mon, 24 Feb 2020 15:13:39 GMT

Anonymous

Im glad to hear!

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