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!

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,

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

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

      • rsbin's avatar
        rsbin
        Community Champion
        5 years ago

        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:

        MonthEndDateClientFacility
        1/31/20211A
        1/31/20211B
        1/31/20212C
        1/31/20212D
        1/31/20212E
        2/28/20211A
        2/28/20211B
        2/28/20212C
        2/28/20212D
        2/28/20212E

        I hope this provides a clearer picture of what I am after.

        Appreciate your patience and thanks again.