Forum Discussion

MarkDGaal's avatar
MarkDGaal
Icon for Helper III rankHelper III
10 years ago
Solved

Merge / Join Conditions and Clause >= or <=

  I'm trying to duplicate this crossjoin I have in Tableau:   Note the Join Clauses of RFP/RFI Release Date >= Date (list of all possible date times starting from the begining of 2015) and  ...
Need Help
  • Eric_Zhang's avatar
    Eric_Zhang
    10 years ago

    MarkDGaal

     

    It looks that you're looking for a LEFT JOIN, if so, instead of using merger queries, I'd prefer DAX. Check if below sample helps.

     

     

    Table =
NATURALLEFTOUTERJOIN (
    opportunities,
    FILTER (
        CROSSJOIN ( opportunities, dates ),
        opportunities[RFP/RFI Release Date ] >= dates[dates]
            && opportunities[Proposal Submissiond Date ] <= dates[dates]
    )
)
MarkDGaal's avatar
MarkDGaal
Icon for Helper III rankHelper III
10 years ago

Thus far I've tried:

let
    Source = #"SRC_crm_pipeline opportunities",
    #"Merged Queries" = Table.NestedJoin(Source,{"RFP/RFI_Release_Date"},#"SRC_crm_pipeline dates",{"Date"},"NewColumn",JoinKind.LeftOuter),
    #"Expanded NewColumn" = Table.ExpandTableColumn(#"Merged Queries", "NewColumn", {"Date", "Day_of_Week", "Day", "Month", "Year", "Holiday", "Workday"}, {"Date", "Day_of_Week", "Day", "Month", "Year", "Holiday", "Workday"}),
    #"Added Custom" = Table.AddColumn(#"Expanded NewColumn", "RFP/RFI >= and Proposal_Submission <=", each if(([#"RFP/RFI_Release_Date"]>=[Date] or null)) then "Yes"
else if(([#"Proposal_Submission_Date_©"]<=[Date] or null)) then "Yes"
else "No"),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([#"RFP/RFI >= and Proposal_Submission <="] = "Yes")),
    #"Changed Type" = Table.TransformColumnTypes(#"Filtered Rows",{{"RFP/RFI >= and Proposal_Submission <=", type text}})
in
    #"Changed Type"

but I believe this is different than the crossjoin as my final tables have vastly differing amounts of records 

Eric_Zhang's avatar
Eric_Zhang
Icon for Microsoft Employee rankMicrosoft Employee
10 years ago

MarkDGaal

 

It looks that you're looking for a LEFT JOIN, if so, instead of using merger queries, I'd prefer DAX. Check if below sample helps.

 

 

Table =
NATURALLEFTOUTERJOIN (
    opportunities,
    FILTER (
        CROSSJOIN ( opportunities, dates ),
        opportunities[RFP/RFI Release Date ] >= dates[dates]
            && opportunities[Proposal Submissiond Date ] <= dates[dates]
    )
)
  • MarkDGaal's avatar
    MarkDGaal
    Icon for Helper III rankHelper III
    10 years ago

    hummmm, are you suggesting that I simply create a new column inside the opportunities table?

    If so I get the following error with that formula:

    • Eric_Zhang's avatar
      Eric_Zhang
      Icon for Microsoft Employee rankMicrosoft Employee
      10 years ago

      MarkDGaal

       

      Can you post some sample data in your case? Even better you can upload a pbix.

      • MarkDGaal's avatar
        MarkDGaal
        Icon for Helper III rankHelper III
        10 years ago

        Eric_Zhang Will do, sanatizing now; as a note I did find this similar thread/question on crossjoins with conditions using m-script/power query: Link answered by ImkeF however they later got into some stuff that I don't quite understand about delimiting with a "|" and don't think I need for my situation.

         

        I'll post an example file with an hour or so. 

         

  • MarkDGaal's avatar
    MarkDGaal
    Icon for Helper III rankHelper III
    10 years ago

    Eric_Zhang Oh...... now I get it.... you wanted me to create a calculated table in the data view. Haven't ever done that before. It works (number of records match the Tableau join), thank you@

     

    Out of curiosity is there a Power Query / M Script Solution to this problem?

  • amshukla's avatar
    amshukla
    Frequent Visitor
    9 years ago

    Hi,

     

    Could you please suggest me how to achive it using power query instead of dax?

     

    Thanks,

    Amit