Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I'm working with tables that I need to join based on a calculated field. I know how to do the calculation in DAX, however, this doesn't help me merge the tables (I need to join on multiple columns so relationships won't work). I know if I can get the following formula into Power Query, I will be able to merge the tables I'd like to but I just can't quite get the formula right. This is what I have in DAX in Table1 and it does exactly what I need it to:
Date = Calculate(Max(Table2[Effective_Term]),Filter('Table2','Table1'[Term]=>'Table2'[Effective_Term]))
Essentially I have two date columns ("Effective_Term" in Table2 and "Term" in Table1) and I need to find the maximum Effective_Term that is less than the Term.For example, Effective_Term contains {201802, 201701, 201601} so for Term=201801 I want to return 201701).
Any help is appreciated!!
Hi @Anonymous
Can you please share data in OneDrive or GoogleDrive and post the link here to find a solution.
Cheers
CheenuSing
I posted some sample data here:
"Course Info" only contains the Effective Term and not the TermCode, so I want to add a column in the "Term Info" table (in query editor) that related TermCode and EffectiveTerm. Then, I would be able to pull in the related TermCode for each EffectiveTerm in "Course Info" and merge "Course Info" and "Scheduling Data" on CourseID and TermCode.
I have the result I would like to have in DAX (see "Term Info", FORMULA_EFF_TERM), however, in order to accompish the merge I need this result column in query mode. Hopefully I explained this clearly but if not please let me know!
Hi @Anonymous
Please let me know if my understanding is right
You want the value of "Term Info"[FORMULA_EFF_TERM] value to be posted in the Course Info table as a new column.
Cheers
CheenuSing
I'd like to have the "Term Info"[FORMULA_EFF_TERM] values to appear in query editor so I'm able to merge tables (I need to link on more than one value.
User | Count |
---|---|
103 | |
88 | |
78 | |
71 | |
69 |
User | Count |
---|---|
113 | |
99 | |
97 | |
72 | |
68 |