SQL to DAX

[Shamatix]

Hey fellow users,

How can I make the following SQL sentence into DAX?

SELECT Kreditor, cvr, creditornumber

FROM [TableA]

WHERE cvr IN (SELECT Vendor_Cvr FROM [TABLEB])

AND name NOT IN (SELECT kreditor FROM TalbeC WHERE id IN (SELECT itemid FROM TableD WHERE filename LIKE '%Html'))

Basicly I want a custom column / measure added to my TableA where it outputs the name if the result is "true" and doesnt output anything if its false

I hope someone can help me out 🙂

Best regards

[Phil_Seamark]

HI @Shamatix

Do you have relationships between your four tables? If so, what is the (1:*) direction?

[Shamatix]

---

@Phil_Seamark wrote:

HI @Shamatix

Do you have relationships between your four tables? If so, what is the (1:*) direction?

---

Hey,

I have actually come a really long way now, but I just need the last brick in my puzzle to fit.

I have 2 tables:

Table A

Column A

Table B

Column B

I now want to create a measure in Table A saying that if Column A exists in Table B Column B then return 1 else 0

I hope you can help.

Best regards

[nirvana_moksh]

Maybe you can utilize the below LOOKUP function in DAX - 

LOOKUPVALUE( <result_columnName>, <search_columnName>, <search_value>[, <search_columnName>, <search_value>]…)

OR 

CALCULATE(COUNTROWS(TABLEA), FILTER(TABLEA, TABLEA[I'D] =TABLEB[I'D])) >0

[Shamatix]

---

@nirvana_moksh wrote:

Maybe you can utilize the below LOOKUP function in DAX - 

LOOKUPVALUE( <result_columnName>, <search_columnName>, <search_value>[, <search_columnName>, <search_value>]…)

OR 

CALCULATE(COUNTROWS(TABLEA), FILTER(TABLEA, TABLEA[I'D] =TABLEB[I'D])) >0

---

Couldnt realyl get either to work sadly =/

[nirvana_moksh]

Did you try it creating it as a measure or a calculated column?

[Shamatix]

---

@nirvana_moksh wrote:

Did you try it creating it as a measure or a calculated column?

---

Calculated column.

If you have skype and want to you can fast help me and see my issue:)

Best regards

[nirvana_moksh]

Can you try this instead - CALCULATE( COUNTROWS('Table1'), FILTER( 'Table1', 'Table1ID' = EARLIER(Table2ID) ) ) > 0

I am not using my desktop and I am replying from my phone so wont be able to do screen share

The 'ID' in the above DAX expression is the column values you are tryign to compare.

[Shamatix]

---

@nirvana_moksh wrote:

Can you try this instead - CALCULATE( COUNTROWS('Table1'), FILTER( 'Table1', 'Table1ID' = EARLIER(Table2ID) ) ) > 0

I am not using my desktop and I am replying from my phone so wont be able to do screen share

The 'ID' in the above DAX expression is the column values you are tryign to compare.

---

Column values? I am trying to compare to entire columns to eachother.

Like for each row if value in Column A matches a value in Column B of another table type 1 else 0:P

The syntax seems way off

[nirvana_moksh]

From your screenshot I see you have too many closing brackets, your closing brackets should match with what I had listed. Also, yes this will work as VLOOKUP in Excel.

[Shamatix]

---

@nirvana_moksh wrote:

From your screenshot I see you have too many closing brackets, your closing brackets should match with what I had listed. Also, yes this will work as VLOOKUP in Excel.

---

Well it adds the last 3 it self

[nirvana_moksh]

Hmm that’s strange but in any regards that solution should work and return TRUE and FALSE which you can later modify easily utilizing a IF statement outside it for replacing it with 0 and 1 values

[Shamatix]

---

@nirvana_moksh wrote:
Hmm that’s strange but in any regards that solution should work and return TRUE and FALSE which you can later modify easily utilizing a IF statement outside it for replacing it with 0 and 1 values

---

As you can see in this video my table B doesnt even come up as an option in the "Earlier" statement only Table A does?

https://www.youtube.com/watch?v=Vly0vImXOEA&feature=youtu.be

[nirvana_moksh]

Do your two tables have a relationship defined?

[Shamatix]

---

@nirvana_moksh wrote:
Do your two tables have a relationship defined?

---

Yes they do, but I cant choose ANY of all the tables related to that main table (items)

[nirvana_moksh · Accepted solution]

Compare = IF(COUNTROWS(FILTER('Table 2',SEARCH('Table2'[ColumnToCompare],Table1[ColumnToCompare],1,0))),"YES","NO")

You can give the above a shot too

[nirvana_moksh]

Try reversing the order of the tables that you have listed for a try, so wherever you had table 1 mentione put table 2 and vice versa.

[Shamatix]

---

@nirvana_moksh wrote:

Try reversing the order of the tables that you have listed for a try, so wherever you had table 1 mentione put table 2 and vice versa.

---

Now it doesnt give me errors as seen below, but it does it completely wrong as you see in the pictures, I just want it to say 1 or 0 based on if the value (Items.Merged) exists in OIO Tabel.Test

[nirvana_moksh]

I am assuming that worked?

[Shamatix]

---

@nirvana_moksh wrote:

I am assuming that worked?

---

Ended up getting it to work, thanks a lot for the help :)

I have another question tho, I have over 300 databases and currently I have to add my user on all the databases one by one to schedule a refresh... is there a simple way of doing this rather than having to add it on more than 300 databases one by one?

[nirvana_moksh]

Wonderful, can you accept it as a solution? And for the 300 databases do you mean the Scheduled Refresh on PBI Service?