Forum Discussion
DAX
Reading over your whole post i'm thinking this should be done in Power Query instead of dax. Never the less, here is the dax for Q1
Q1: Create a Custom Column:
Status = var thisRecord = [ID] RETURN CALCULATE( COUNTROWS('Table2'), ALL('Table2'), 'Table2'[ID] = thisRecord ) > 0Q2: I really really stress this is going to be a bad idea. The 'Edit Queries' are of Power BI is much better suited to creating tables. Q1 could also be achieved there as well.
Is there a specific reason you want to do this with Dax?
Reading over your whole post i'm thinking this should be done in Power Query instead of dax. Never the less, here is the dax for Q1
Q1: Create a Custom Column:
Status = var thisRecord = [ID]
RETURN
CALCULATE(
COUNTROWS('Table2'),
ALL('Table2'),
'Table2'[ID] = thisRecord
) > 0
Q2: I really really stress this is going to be a bad idea. The 'Edit Queries' are of Power BI is much better suited to creating tables. Q1 could also be achieved there as well.
Is there a specific reason you want to do this with Dax?
I dont have any problem to try other ways other than DAX.Please suggest the best approach.
How can we create a new table from two existing tables using edit queries?
In the Edit Queries section, if you right click on any query (a table), there is an option called "Reference".
This creates a new table that begins as a reference to that other table. From here you can remove rows, add new columns and do all of the general import manipulate you like. When you click close and apply, you will see it as its own table, named however you wished.
Thank You for your reply.The DAX is working fine.How to compare the a substring value of table2[ID] ?
Can you please include the SEARCH function in the given DAX.(thisRecord will be available as a substring in Table2[ID].)
Status = var thisRecord = [ID]
RETURN
CALCULATE(
COUNTROWS('Table2'),
ALL('Table2'),
'Table2'[ID] = thisRecord
) > 0How can we implement this in Power Query?
I haven't got a quick database to test to give you the exact code, but my expectation is that you would do a "Merge Queries" between the 2 tables. This should give you a field that you would expand under normal circumstances. Instead I would attempt using the function List.Count to count how many entries exist in that expandable field.
https://msdn.microsoft.com/en-us/query-bi/m/list-count
If that doesn't work, i'm sure there is another function that will do the count for you. You could then remove the 'merge' column after this to keep your data model small.
