Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Possible to copy a large table changing only one element?

I am creating a pretty large report that pulls similar data from multiple sources. I have worked out a table that has dozens of coumns, each with a lot of DAX of varying sophistication. I need to cre...
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    HI Anonymous,

    I also think union functions with selectcolumns show suitable for your scenarios.
    You can use them to selectcolumns to pick up the table and change some of the value of their fields or add expression to calculate, the union function will merge them as a new table. You can write DAX expression on it instead of duplicate these formulas on each table.

    New Table =
UNION (
    SELECTCOLUMNS (
        Table1,
        "ID", [ID],
        "Date", [Create],
        "Price", [Price],
        "Amount", [Sales]
    ),
    SELECTCOLUMNS (
        Table2,
        "ID", [ID],
        "Date", [DateIn],
        "Price", [Price],
        "Amount", [Qty]
    ),
    SELECTCOLUMNS (
        Table3,
        "ID", [ID],
        "Date", [Finish],
        "Price", [Price],
        "Amount", [Value]
    )
)

    Reference links of Dax functions:

    SELECTCOLUMNS 

    UNION 

    BTW, you can also add filters nested into selectcolumns table expressions to filter records if there existed some of the records you not want them to show into the merged new table.

    Regards,

    Xiaoxin Sheng

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
5 years ago

Anonymous , union with summarize and SELECTCOLUMNS  , there you can change the value , In summarize keep that column at last 

Table = union(
SELECTCOLUMNS(Instagram, "Index",[Index], "row",[Row])
,SELECTCOLUMNS(Instagram, "Index",[Index], "row",1)
)

 

 

also refer: https://www.sqlbi.com/articles/from-sql-to-dax-joining-tables/

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    I am not entirely sure I am understanding your suggestion, but it could just be my limited Power BI experience.

    Let's say for example I have a table with one column, and the column has the DAX shown below, but I want to replace every instance of REV (irrespective of where it falls in the expression) with RFV.

    What would the UNION look like?

     

    G1 =
    CALCULATE(
    SUM('Causals'[Quantity]),
    FILTER('Causals', 'Causals'[Test Month] = 'Level 1 REV'[Month] && 'Causals'[Test Year] = 'Level 1 REV'[Calendar Year] && 'Causals'[Site] = "REV" && 'Causals'[Causal Lvl 1] = "G1"))

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      HI Anonymous,

      I also think union functions with selectcolumns show suitable for your scenarios.
      You can use them to selectcolumns to pick up the table and change some of the value of their fields or add expression to calculate, the union function will merge them as a new table. You can write DAX expression on it instead of duplicate these formulas on each table.

      New Table =
UNION (
    SELECTCOLUMNS (
        Table1,
        "ID", [ID],
        "Date", [Create],
        "Price", [Price],
        "Amount", [Sales]
    ),
    SELECTCOLUMNS (
        Table2,
        "ID", [ID],
        "Date", [DateIn],
        "Price", [Price],
        "Amount", [Qty]
    ),
    SELECTCOLUMNS (
        Table3,
        "ID", [ID],
        "Date", [Finish],
        "Price", [Price],
        "Amount", [Value]
    )
)

      Reference links of Dax functions:

      SELECTCOLUMNS 

      UNION 

      BTW, you can also add filters nested into selectcolumns table expressions to filter records if there existed some of the records you not want them to show into the merged new table.

      Regards,

      Xiaoxin Sheng