The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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?
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
95 | |
93 | |
85 | |
68 | |
65 |
User | Count |
---|---|
241 | |
124 | |
120 | |
81 | |
79 |