DAX query to compare two columns from different tables

[MattAdams]

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!

[Greg_Deckler]

Generally you use RELATED or RELATEDTABLE.

[MattAdams]

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?

[Greg_Deckler]

Here's an example:

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

You could also possibly use LOOKUPVALUE.

[Anonymous]

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!

[Ashish_Mathur]

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.

[DAXRichArd]

LOOKUPVALUE() worked for me. Thx!

[DAXRichArd]

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

[MattAdams]

@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.

[Greg_Deckler]

@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])

[MattAdams]

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. 

[Greg_Deckler]

@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.

[MattAdams]

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.

[Greg_Deckler · Accepted solution]

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

[MattAdams]

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?

[MattAdams]

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

[Greg_Deckler]

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.

[MattAdams]

NOW_TASK_B_WORK_START_DTTM	NOW_TASK_M_ETL_LOAD_DTTM	CurrentDateTime	ResolvedDtTmWithNullLogic	DurationDaysOpen	2 - 5 Days	5 - 7  Days	7 - 14 Days	14 - 28 Days	28 - 60 Days	60+ Days	ClosedDtTm	Same Day	Is it Open	1 - 2 Days
	2/14/2018 10:05	2/19/2018 0:00	Wednesday, November 8, 2017	7.067465	0	0	1	0	0	0	11/8/2017 17:00	0	N	0
	2/14/2018 10:05	2/19/2018 0:00	Saturday, August 12, 2017	7.027755	0	0	1	0	0	0	8/12/2017 1:00	0	N	0
	2/14/2018 10:05	2/19/2018 0:00	Monday, October 23, 2017	7.059583	0	0	1	0	0	0	10/23/2017 17:00	0	N	0
	2/14/2018 10:05	2/19/2018 0:00	Monday, August 14, 2017	7.025729	0	0	1	0	0	0	8/14/2017 18:00	0	N	0

[MattAdams]

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

ROW_WID

CALENDAR_DATE

CAL_MONTH_NBR

CAL_QTR_NBR

CAL_WEEK_NBR

CAL_YEAR_NBR

CAL_DAY_OF_WEEK_NBR

CAL_DAY_NM

CAL_DAY_OF_MONTH_NBR

MONTH_NM

MONTH_NM_SHRT

QUARTER_NM

QUARTER_SHRT_NM

YEAR_HH_NM

YY-QQ

YY-MM

MM-MONTH_NM

WEEK_STARTING_SUN

WEEK_STARTING_MON

WEEK_ENDING_FRI

WEEK_ENDING_SUN

Same Day

1 - 2 Days

2 - 5 Days

5 - 7 Days

7 - 14 Days

14 - 28 Days

28 - 60 Days

60+ Days

7 - 14 Days 2nd

20170101

Sunday, January 1, 2017

1

1

1

2017

1

Sunday

1

January

Jan

First Quarter

Q1

2017-1H

2017-Q1

2017-01

1-January

Sunday, January 1, 2017

Monday, January 2, 2017

Friday, January 6, 2017

########

8

20170102

Monday, January 2, 2017

1

1

1

2017

2

Monday

2

January

Jan

First Quarter

Q1

2017-1H

2017-Q1

2017-01

1-January

Sunday, January 1, 2017

Monday, January 2, 2017

Friday, January 6, 2017

########

8

20170103

Tuesday, January 3, 2017

1

1

1

2017

3

Tuesday

3

January

Jan

First Quarter

Q1

2017-1H

2017-Q1

2017-01

1-January

Sunday, January 1, 2017

Monday, January 2, 2017

Friday, January 6, 2017

########

8

20170104

Wednesday, January 4, 2017

1

1

1

2017

4

Wednesday

4

January

Jan

First Quarter

Q1

2017-1H

2017-Q1

2017-01

1-January

Sunday, January 1, 2017

Monday, January 2, 2017

Friday, January 6, 2017

########

8

20170105

Thursday, January 5, 2017

1

1

1

2017

5

Thursday

5

January

Jan

First Quarter

Q1

2017-1H

2017-Q1

2017-01

1-January

Sunday, January 1, 2017

Monday, January 2, 2017

Friday, January 6, 2017

########

9

20170106

Friday, January 6, 2017

1

1

1

2017

6

Friday

6

January

Jan

First Quarter

Q1

2017-1H

2017-Q1

2017-01

1-January

Sunday, January 1, 2017

Monday, January 2, 2017

Friday, January 6, 2017

########

9

20170107

Saturday, January 7, 2017

1

1

1

2017

7

Saturday

7

January

Jan

First Quarter

Q1

2017-1H

2017-Q1

2017-01

1-January

Sunday, January 1, 2017

Monday, January 2, 2017

Friday, January 6, 2017

########

9

20170108

Sunday, January 8, 2017

1

1

2

2017

1

Sunday

8

January

Jan

First Quarter

Q1

2017-1H

2017-Q1

2017-01

1-January

Sunday, January 8, 2017

Monday, January 9, 2017

Friday, January 13, 2017

########

9

20170109

Monday, January 9, 2017

1

1

2

2017

2

Monday

9

January

Jan

First Quarter

Q1

2017-1H

2017-Q1

2017-01

1-January

Sunday, January 8, 2017

Monday, January 9, 2017

Friday, January 13, 2017

########

9

20170110

Tuesday, January 10, 2017

1

1

2

2017

3

Tuesday

10

January

Jan

First Quarter

Q1

2017-1H

2017-Q1

2017-01

1-January

Sunday, January 8, 2017

Monday, January 9, 2017

Friday, January 13, 2017

########

9

20170111

Wednesday, January 11, 2017

1

1

2

2017

4

Wednesday

11

January

Jan

First Quarter

Q1

2017-1H

2017-Q1

2017-01

1-January

Sunday, January 8, 2017

Monday, January 9, 2017

Friday, January 13, 2017

########

9

[MattAdams]

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