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!

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

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'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

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

  • 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

         

         

         

    • 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!

  • DAXRichArd's avatar
    DAXRichArd
    Icon for Resolver I rankResolver I
    7 years ago

    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

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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's avatar
      Ashish_Mathur
      Icon for Super User rankSuper User
      6 years ago

      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's avatar
        DAXRichArd
        Icon for Resolver I rankResolver I
        6 years ago

        LOOKUPVALUE() worked for me. Thx!

  • Zuza's avatar
    Zuza
    Frequent Visitor
    1 year ago

    you just saved me a ton of work, ghatgpt suck's!!!!