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 ,

 

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.

       

      • rsbin's avatar
        rsbin
        Community Champion
        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!