Forum Discussion
Community ChampionAddColumns from Another Table
Was finally able to solve this with the CROSSJOIN function. Had to first create an intermediate table with the Distinct Client - Facility combinations I needed. I'm sure there must be a way to do this in one step. It may not be the cleanest solution, but at least it works for now.
=CROSSJOIN( FILTER ( SELECTCOLUMNS ( DIM_Date_Slicer, "DateKey", DIM_Date_Slicer[DateKey], "MonthEndDate", DIM_Date_Slicer[Date] ), [MonthEndDate] = EOMONTH ( [MonthEndDate], 0 )), FactLiftClientFacility )Thanks again for your efforts!
Community ChampionHere is a simplified view of my model. Please bear in mind this is an SSAS model that I am working with.
As above, I have created the Month End Dates. So for each unique combination of Client and Facility (example above), I want to join to my Month End Date. Final Result expected is:
| MonthEndDate | Client | Facility |
| 1/31/2021 | 1 | A |
| 1/31/2021 | 1 | B |
| 1/31/2021 | 2 | C |
| 1/31/2021 | 2 | D |
| 1/31/2021 | 2 | E |
| 2/28/2021 | 1 | A |
| 2/28/2021 | 1 | B |
| 2/28/2021 | 2 | C |
| 2/28/2021 | 2 | D |
| 2/28/2021 | 2 | E |
I hope this provides a clearer picture of what I am after.
Appreciate your patience and thanks again.
Community ChampionWas finally able to solve this with the CROSSJOIN function. Had to first create an intermediate table with the Distinct Client - Facility combinations I needed. I'm sure there must be a way to do this in one step. It may not be the cleanest solution, but at least it works for now.
=CROSSJOIN(
FILTER (
SELECTCOLUMNS (
DIM_Date_Slicer,
"DateKey", DIM_Date_Slicer[DateKey],
"MonthEndDate", DIM_Date_Slicer[Date] ),
[MonthEndDate] = EOMONTH ( [MonthEndDate], 0 )),
FactLiftClientFacility )Thanks again for your efforts!