Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
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
Solved! Go to Solution.
Compare = IF(COUNTROWS(FILTER('Table 2',SEARCH('Table2'[ColumnToCompare],Table1[ColumnToCompare],1,0))),"YES","NO")
You can give the above a shot too
HI @Shamatix
Do you have relationships between your four tables? If so, what is the (1:*) direction?
@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
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
@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 =/
Did you try it creating it as a measure or a calculated column?
@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
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.
@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
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.
@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 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 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)
Compare = IF(COUNTROWS(FILTER('Table 2',SEARCH('Table2'[ColumnToCompare],Table1[ColumnToCompare],1,0))),"YES","NO")
You can give the above a shot too
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.
@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
I am assuming that worked?
@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?
Wonderful, can you accept it as a solution? And for the 300 databases do you mean the Scheduled Refresh on PBI Service?
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
72 | |
68 | |
53 | |
39 | |
33 |
User | Count |
---|---|
70 | |
63 | |
57 | |
49 | |
46 |