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

DAX query to compare two columns from different tables

I have a join on Column A and Column B, from respectively Table A and Table B. Both contain date values. From DAX via table A, I'd like to say if the date of Column B is to Column A or fell between the date of Column A and Column B , 1, else 0.

 

I can't seem to find the right function/formula to allow via DAX for any DAX queries to "see" column b from a DAX query within table A. Hopefully that makes sense. Many thanks in advance!

1 ACCEPTED SOLUTION

OK, I read that 3 times and I'm not following it. Can you explain it a different way? Sorry!


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

19 REPLIES 19
Greg_Deckler
Super User IV
Super User IV

Generally you use RELATED or RELATEDTABLE.


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Thanks @Greg_Deckler I see that RELATEDTABLE give you the option to reference another table, but how do I refer to tableB.columnB from tableA.columnA?

Here's an example:

 

Column = IF(RELATED('OtherTable'[ColumnB])=[ColumnA],1,0)

You could also possibly use LOOKUPVALUE.


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Anonymous
Not applicable

Hi,

 

I have a similar question: 

I have WeekStart dates in 'Dates' table and another table (Table B) with various random WeekStart dates.

In Table B, if the WeekStart is in Table A, I need to populate 1 in a column, else 0. 

 

Any leads appreciated!

Hi,

RELATED(), RELATEDTABLE() or LOOKUPVALUE() should work.  To get specific help, share your data in a form that can be pasted in an MS Excel file and show the expected result.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

LOOKUPVALUE() worked for me. Thx!

Thx for the LOOKUPVALUE solution Gregg,

My problem solving methodology is to hack away and solving my problem. Then I troll our Power BI community. Last I'll post a plea for help. In this case I didn't have to post because I found a solution through researching this forum.

THX Again!

RM

@Greg_Deckler Greetings sir. I'm a bit stuck (i am in my first 6 months of pbi bare with me). I have two tables, date (first pic) and task tables (2nd pic). I'm trying to determine from the date table if the date range between "opened date" column, and "resolved date" column (2nd pic) on each record falls on on each calendar date, and if so, make a new column have a value of 1, otherwise 0. I know this may be confusing, but I can't seem to figure out what to use. Its ultimate goal is for looking at aging work that's been open, and seeing how many were open at each day in history if that makes sense. If you can give me your ideas, you're an expert. We are stumped at my company. We are going off the calendar_date column in the date table.snip_20180219135033.pngsnip_20180219135212.png

@MattAdams- OK, I created a Calendar table called Calendar like this:

 

Calendar = CALENDAR(DATE(2018,1,1),DATE(2018,12,31))

Then I created an Enter Data query like this for a table called Issues:

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTLUN9Q3MjC0ADONIcxYnWglI7CAEULOFCFnDBYwQ8iZQ+ViAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Issue = _t, #"Issue Start" = _t, #"Issue End" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Issue", Int64.Type}, {"Issue Start", type date}, {"Issue End", type date}})
in
    #"Changed Type"

Then I could create this column in the Calendar table:

 

Issues = COUNTX(FILTER(Issues,Issues[Issue Start]<=[Date] && Issues[Issue End]>=[Date]),Issues[Issue])

 

 

 

 


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




This is a very useful query and is accurate, but I may ask a bit more of your brain. Many thanks. One quick question. Is there any way I could add onto this to give me a count of ones that if I needed to create "buckets", like "1-2 days" and "2-5 days", if I had another column called business duration that listed a value like 2.05 which would indicate work in the 2-5 Day bucket on calendar_date Jan 1 2018? If I looked at the query and said "give me the count of how many issues were open on say, Jan 1 2018 that fall into the 2-5 day bucket when column business duration is between 2 and 5 and fitting the existing logic of 

Issues[Issue Start]<=[Date] && Issues[Issue End]>=[Date])

 I know this is getting complex, but we have a rather complex request here to generate this. 

@MattAdams- Sure, you could create a column in your Issues table that was simply something like 

Issue Duration = [Issue End] - [Issue Start] * 1.

You could also use DATEDIFF. Then you could change your filter like:

 

 

Issues[Issue Start]<=[Date] && Issues[Issue End]>=[Date] && Issues[Issue Duration] > 2 && Issues[Issue Duration] < 5 )

Stuff like that. You can even make it really complex by mixing && (AND) and || (OR) clauses and put in parathesis and all kinds of stuff.


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Many Thanks sir. One last question, then I think I accept as solution. Do you know of a formula I can reach over into my Issues table from my date table look at the open and closed date on the issue and refer back to the date table "calendar_date" of say 1/1/2018 and do a operator that will essentially says "how many were open this calendar_date between these two open and closed dates? I've been looking for one that I can reference from the date table to the issues table, and can't find one just yet for this. Maybe its just me.

OK, I read that 3 times and I'm not following it. Can you explain it a different way? Sorry!


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

oh no worries! my biggest dillemma with this darn report is now that I can tell what issues were open for these calendar_date's, I'm trying to determine if they were in an open status for each calendar_date, and how many were open. We are trending by the "buckets" of time per day throughout the year. I can't effectively use the state of the issue because we are looking back in time and generally speaking they're all in a resolved state today, but looking back 3 months ago would like to show how many were not resolved yet, by using the resolved date and opened date. Does that help? I do have it counting the amount of incidents between the open and closed date successfully, but have a bigger number than is accurate by telling which are essentially not resolved yet and still an open work for those days. Does that help any?

Let me run with this and give it a try! Thanks for everything you do!

This sounds very similar to something I put together for a call center in terms of open tickets at any particular time. If you can paste in some sample data that I can copy and paste I could create the model/measures for you more specifically. Give me a minute though and I'll see if I can mock something up.

 


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




NOW_TASK_B_WORK_START_DTTMNOW_TASK_M_ETL_LOAD_DTTMCurrentDateTimeResolvedDtTmWithNullLogicDurationDaysOpen2 - 5 Days5 - 7  Days7 - 14 Days14 - 28 Days28 - 60 Days60+ DaysClosedDtTmSame DayIs it Open1 - 2 Days
 2/14/2018 10:052/19/2018 0:00Wednesday, November 8, 20177.06746500100011/8/2017 17:000N0
 2/14/2018 10:052/19/2018 0:00Saturday, August 12, 20177.0277550010008/12/2017 1:000N0
 2/14/2018 10:052/19/2018 0:00Monday, October 23, 20177.05958300100010/23/2017 17:000N0
 2/14/2018 10:052/19/2018 0:00Monday, August 14, 20177.0257290010008/14/2017 18:000N0

WIsh I could upload a xls. Here's pasted. First date table, then task:

ROW_WIDCALENDAR_DATECAL_MONTH_NBRCAL_QTR_NBRCAL_WEEK_NBRCAL_YEAR_NBRCAL_DAY_OF_WEEK_NBRCAL_DAY_NMCAL_DAY_OF_MONTH_NBRMONTH_NMMONTH_NM_SHRTQUARTER_NMQUARTER_SHRT_NMYEAR_HH_NMYY-QQYY-MMMM-MONTH_NMWEEK_STARTING_SUNWEEK_STARTING_MONWEEK_ENDING_FRIWEEK_ENDING_SUNSame Day1 - 2 Days2 - 5 Days5 - 7 Days7 - 14 Days14 - 28 Days28 - 60 Days60+ Days7 - 14 Days 2nd
20170101Sunday, January 1, 201711120171Sunday1JanuaryJanFirst QuarterQ12017-1H2017-Q12017-011-JanuarySunday, January 1, 2017Monday, January 2, 2017Friday, January 6, 2017########       8 
20170102Monday, January 2, 201711120172Monday2JanuaryJanFirst QuarterQ12017-1H2017-Q12017-011-JanuarySunday, January 1, 2017Monday, January 2, 2017Friday, January 6, 2017########       8 
20170103Tuesday, January 3, 201711120173Tuesday3JanuaryJanFirst QuarterQ12017-1H2017-Q12017-011-JanuarySunday, January 1, 2017Monday, January 2, 2017Friday, January 6, 2017########       8 
20170104Wednesday, January 4, 201711120174Wednesday4JanuaryJanFirst QuarterQ12017-1H2017-Q12017-011-JanuarySunday, January 1, 2017Monday, January 2, 2017Friday, January 6, 2017########       8 
20170105Thursday, January 5, 201711120175Thursday5JanuaryJanFirst QuarterQ12017-1H2017-Q12017-011-JanuarySunday, January 1, 2017Monday, January 2, 2017Friday, January 6, 2017########       9 
20170106Friday, January 6, 201711120176Friday6JanuaryJanFirst QuarterQ12017-1H2017-Q12017-011-JanuarySunday, January 1, 2017Monday, January 2, 2017Friday, January 6, 2017########       9 
20170107Saturday, January 7, 201711120177Saturday7JanuaryJanFirst QuarterQ12017-1H2017-Q12017-011-JanuarySunday, January 1, 2017Monday, January 2, 2017Friday, January 6, 2017########       9 
20170108Sunday, January 8, 201711220171Sunday8JanuaryJanFirst QuarterQ12017-1H2017-Q12017-011-JanuarySunday, January 8, 2017Monday, January 9, 2017Friday, January 13, 2017########       9 
20170109Monday, January 9, 201711220172Monday9JanuaryJanFirst QuarterQ12017-1H2017-Q12017-011-JanuarySunday, January 8, 2017Monday, January 9, 2017Friday, January 13, 2017########       9 
20170110Tuesday, January 10, 201711220173Tuesday10JanuaryJanFirst QuarterQ12017-1H2017-Q12017-011-JanuarySunday, January 8, 2017Monday, January 9, 2017Friday, January 13, 2017########       9 
20170111Wednesday, January 11, 201711220174Wednesday11JanuaryJanFirst QuarterQ12017-1H2017-Q12017-011-JanuarySunday, January 8, 2017Monday, January 9, 2017Friday, January 13, 2017########       9

 

 

 

thanks..ill head down this path and update back to you accordingly. Thank you sir.

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

March Update

Check it Out!

Click here to read more about the March 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.