Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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!
Solved! Go to Solution.
OK, I read that 3 times and I'm not following it. Can you explain it a different way? Sorry!
Generally you use RELATED or RELATEDTABLE.
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.
you just saved me a ton of work, ghatgpt suck's!!!!
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.
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.
@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])
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.
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!
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.
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 |
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 |
|
thanks..ill head down this path and update back to you accordingly. Thank you sir.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
92 | |
86 | |
85 | |
66 | |
49 |
User | Count |
---|---|
140 | |
113 | |
106 | |
64 | |
60 |