cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Surya1
Helper I
Helper I

DAX

Hi All,

 

Someone please help

 

I have 2 questions.

 

Q1.There are 2 tables,Table 1 and Table 2 with a unique column called ID.I want to add a column called status in Table 1 by matching the ID columns of Table 1 and Table2.If a match is found then add True to the new column and for unmatched ID rows add False to the new column.How can this be done.

 

 

 

Q2.Now from Table1 I need to create a new table Table 3 with columns ID and status which contains all rows from Table 1 which has a particular value for status column.

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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? 

View solution in original post

10 REPLIES 10
Anonymous
Not applicable

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? 

View solution in original post

Status = var thisRecord = [ID]
RETURN
CALCULATE(
COUNTROWS('Table2'),
ALL('Table2'),
'Table2'[ID] = thisRecord
) > 0

 

How can we implement this in Power Query?

Anonymous
Not applicable

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.

Can you please include the SEARCH function in the given DAX.(thisRecord will be available as a substring in Table2[ID].)

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?

Anonymous
Not applicable

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] ?

Anonymous
Not applicable

I think the answer in this thread might be along the lines of what you are looking for:

 

http://community.powerbi.com/t5/Desktop/If-text-column-CONTAINS-specified-value-give-me-what-I-want/...

Thank you.How can i get current date and first working day of a month in DAX?

Anonymous
Not applicable

the formula TODAY() will give you todays date.

 

If you want the first working day in the month, you will need create a Date Table and create a column in that date table that tracks day of the week.  From there, you should be able to use something that contains the date table to the month you care about (either via a Calcualte Statement or from a slicer) and then you need to use  FIRSTDATE inside a calculate statement that constrains on only the weekdays you care about.

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors