Create a measure that combines measures into same column

[Anonymous]

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 MEASURE	Count Measure
INT0	570

Table 2 

INT RANK Column	Count Measure
INT1	20
INT2	40
INT3	30
INT4	0
INT5	10

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

Table 3

INT RANK	COUNT
INT0	570
INT1	20
INT2	40
INT3	30
INT4	0
INT5	10

[v-yingjl]

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.

[amitchandak · Accepted solution]

@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])