Forum Discussion

MattAdams's avatar
MattAdams
Icon for Helper I rankHelper I
8 years ago
Solved

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 t...
Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    8 years ago

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

Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
8 years ago

Here's an example:

 

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

You could also possibly use LOOKUPVALUE.

MattAdams's avatar
MattAdams
Icon for Helper I rankHelper I
8 years ago

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's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    8 years ago

    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's avatar
      MattAdams
      Icon for Helper I rankHelper I
      8 years ago

      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

       

       

       

    • MattAdams's avatar
      MattAdams
      Icon for Helper I rankHelper I
      8 years ago
      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
  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    8 years ago

    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's avatar
      MattAdams
      Icon for Helper I rankHelper I
      8 years ago

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

    • MattAdams's avatar
      MattAdams
      Icon for Helper I rankHelper I
      8 years ago

      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's avatar
        Greg_Deckler
        Icon for Community Champion rankCommunity Champion
        8 years ago

        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.