Forum Discussion

adeel726's avatar
adeel726
Frequent Visitor
6 years ago
Solved

DISTINCT UNION & Summarized Combined.

Hi    I have two tables and i am trying to make a summary table using "New Table". The result of the two tables should be something similar to "group by" function bases on both tables. the uniqunes...
Need Help
  • v-lid-msft's avatar
    v-lid-msft
    6 years ago

    Hi adeel726 ,

     

    we can create a calculated table to meet your requirement:

     

    NewTable = 
ADDCOLUMNS (
    DISTINCT (
        UNION (
            SELECTCOLUMNS ( 'Table1', "Material", [Material], "Period", [Period] ),
            SELECTCOLUMNS ( 'Table2', "Material", [Material], "Period", [Period] )
        )
    ),
    "Prod Order",
    VAR M = [Material]
    VAR P = [Period]
    RETURN
        CALCULATE (
            SUM ( 'Table1'[Production Order] ),
            FILTER ( 'Table1', 'Table1'[Material] = M && 'Table1'[Period] = P )
        ),
    "Planned Order",
    VAR M = [Material]
    VAR P = [Period]
    RETURN
        CALCULATE (
            SUM ( 'Table1'[Planned Order] ),
            FILTER ( 'Table1', 'Table1'[Material] = M && 'Table1'[Period] = P )
        ),
    "Delivery Quantity",
    VAR M = [Material]
    VAR P = [Period]
    RETURN
        CALCULATE (
            SUM ( 'Table2'[Delivered Quantity] ),
            FILTER ( 'Table2', 'Table2'[Material] = M && 'Table2'[Period] = P )
        )
)

     

     


    Best regards,

     

parry2k's avatar
parry2k
Icon for Super User rankSuper User
6 years ago

adeel726 instead of summarizing the tables, the best practice would be to add part no and date table in your model and set relationship of these tables with your these transactio tables and from there you can easily visualize the data the way you want.