Forum Discussion
AddColumns from Another Table
Good Morning,
I need to create a Summary Table. I've got the first part which is creating a Table with Month End Dates from my Date Table:
=
FILTER (
SELECTCOLUMNS (
DIM_Date_Slicer,
"DateKey", DIM_Date_Slicer[DateKey],
"MonthEndDate", DIM_Date_Slicer[Date],
Fact
),
[MonthEndDate] = EOMONTH ( [MonthEndDate], 0 )
)
Kudos to @AIB who provided this code I found in another thread.
I now want to add two columns - ClientName and FacilityName - from a table called 'FactFacilityNames'. I have not been able to figure out the proper syntax to accomplish this.
As always, appreciate the help from the Community!
Best Regards,
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!
7 Replies
Hey rsbin ,
you can add new columns with the ADDCOLUMNS function:
= ADDCOLUMNS( FILTER( SELECTCOLUMNS( DIM_Date_Slicer, "DateKey", DIM_Date_Slicer[DateKey], "MonthEndDate", DIM_Date_Slicer[Date] ), [MonthEndDate] = EOMONTH ( [MonthEndDate], 0 ) ), "ClientName", [YourMeasureOrFormulaThatReturnsTheRightClient], "FacilityName", [YourMeasureOrFormulaThatReturnsTheRightFacility] )To develop results like this DAX Studio is an amazing help, just wanted to mention that 😉
If you need any help please let me know.If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍Best regardsDenisBlog: WhatTheFact.biFollow me: twitter.com/DenSelimovic
