Forum Discussion

rsbin's avatar
rsbin
Community Champion
5 years ago
Solved

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_Slice...
Need Help
  • rsbin's avatar
    rsbin
    5 years ago

    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!

selimovd's avatar
selimovd
Most Valuable Professional
5 years ago

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
 
  • rsbin's avatar
    rsbin
    Community Champion
    5 years ago

    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?

    ClientFacility
    1A
    1B
    2C
    2D
    2E

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

    • selimovd's avatar
      selimovd
      Most Valuable Professional
      5 years ago

      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's avatar
        rsbin
        Community Champion
        5 years ago

        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,