AddColumns from Another Table

[rsbin]

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,

[selimovd]

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 regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[rsbin]

@selimovd,

Thanks for the quick reply, but I don't have a Measure for these other two fields.

For each Month End Date, I need to add each Client and Facility combination.  I think this is where I am still stuck?

Client	Facility
1	A
1	B
2	C
2	D
2	E

Any additional words of wisdom, please.  And thanks again.

[selimovd]

Hey @rsbin ,

I don't know your data model.

If you provide more information (PBIX file, data model, tables, relationships) I can help you. But with only your formula I cannot tell you how to add the other tables.

Best regards

Denis

[rsbin]

@selimovd,

Appreciate the efforts on your part.  It will take me some time to cleanse my model enough so that I can try to post it.  Will let you know once I am able to do so.

Thanks again and Best Regards,

[selimovd]

@rsbin Let me know when you're ready. I'm optimistic we can find a solution.

[rsbin]

@selimovd,

Here 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.

[rsbin · Accepted solution]

@selimovd,

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!