Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Create a measure that combines measures into same column

Hi everyone,

 

I need to create a measure that combines column values and measures into one table. I had to create custom measures to get a custom count value for INT0 from another table. 

 

Table 1

INTRANK MEASURECount Measure
INT0570

 

Table 2 

INT RANK ColumnCount Measure
INT120
INT240
INT330
INT40
INT510

 

Desired outcome appends both together with both measures combined in same columns

Table 3

INT RANKCOUNT
INT0570
INT120
INT2

40

INT3

30

INT4

0

INT5

10

Need Help
  • amitchandak's avatar
    amitchandak
    6 years ago

    Anonymous ,

    Try

    Create a new Table

     

    Union(
selectcolumns(Table1, Table1[INTRANK MEASURE],"Count Measure" , sum(Table1[Count Measure])),
selectcolumns(Table2, Table2[INTRANK MEASURE], "Count Measure", sum(Table2[Count Measure]))
)
OR
Union(
selectcolumns(Table1, "INTRANK MEASURE",Table1[INTRANK MEASURE],"Count Measure" ,Table1[Count Measure]),
selectcolumns(Table2,"INTRANK MEASURE" ,Table2[INTRANK MEASURE],"Count Measure" ,Table2[Count Measure])
)

     

     

    OR a new dimension table and join with both the tables

     

    Union(
all(Table1[INTRANK MEASURE]),
all(Table2[INTRANK MEASURE])
)

     

    And create a new measure

    Count = sum(Table1[Count Measure])+ sum(Table2[Count Measure])

     

     

2 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    Anonymous ,

    Try

    Create a new Table

     

    Union(
selectcolumns(Table1, Table1[INTRANK MEASURE],"Count Measure" , sum(Table1[Count Measure])),
selectcolumns(Table2, Table2[INTRANK MEASURE], "Count Measure", sum(Table2[Count Measure]))
)
OR
Union(
selectcolumns(Table1, "INTRANK MEASURE",Table1[INTRANK MEASURE],"Count Measure" ,Table1[Count Measure]),
selectcolumns(Table2,"INTRANK MEASURE" ,Table2[INTRANK MEASURE],"Count Measure" ,Table2[Count Measure])
)

     

     

    OR a new dimension table and join with both the tables

     

    Union(
all(Table1[INTRANK MEASURE]),
all(Table2[INTRANK MEASURE])
)

     

    And create a new measure

    Count = sum(Table1[Count Measure])+ sum(Table2[Count Measure])

     

     

  • v-yingjl's avatar
    v-yingjl
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi Anonymous ,

    You can try this measure to create a new table:

    Table 3 =
UNION (
    SELECTCOLUMNS (
        'Table',
        "INT RANK", 'Table'[INTRANK MEASURE],
        "COUNT", 'Table'[Count Measure]
    ),
    SELECTCOLUMNS (
        'Table 2',
        "INT RANK", 'Table 2'[INT RANK Column],
        "COUNT", 'Table 2'[Count Measure]
    )
)

     You will get the expected result:

     

    Best Regards,
    Yingjie Li

    If this post helps then please consider Accept it as the solution to help the other members find it more quickly.