Forum Discussion

KristinG's avatar
KristinG
Frequent Visitor
5 years ago
Solved

Combine data from multiple columns

Hello!  I have a table that shows different products and the material value that is bought in different currencies. The value is displayed in one column with the currency in another column. Each pro...
Need Help
  • v-angzheng-msft's avatar
    v-angzheng-msft
    5 years ago

     Hi, KristinG 

     

    Thanks for the ideas provided by DataZoe , I tried to make some changes to the formula

    Table2 =
SUMMARIZE (
    UNION (
        SELECTCOLUMNS (
            'Table',
            "Product", 'Table'[Product ID],
            "Name", 'Table'[Name],
            "Currency", 'Table'[Currency 1],
            "Value", 'Table'[Material value 1]
        ),
        SELECTCOLUMNS (
            'Table',
            "Product", 'Table'[Product ID],
            "Name", 'Table'[Name],
            "Currency", 'Table'[Currency 2],
            "Value", 'Table'[Material value 2]
        ),
        SELECTCOLUMNS (
            'Table',
            "Product", 'Table'[Product ID],
            "Name", 'Table'[Name],
            "Currency", 'Table'[Currency 3],
            "Value", 'Table'[Material value 3]
        )
    ),
    [Currency],
    [Value]
)

    Result:

     

    Table2:

    Visual:

    Please refer to the attachment below for details

     

     

     

    Best Regards,
    Community Support Team _ Zeon Zheng
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

DataZoe's avatar
DataZoe
Microsoft Employee
5 years ago

KristinG I think this will work for direct query, but you can create a DAX table to make a "Currency" table, then use this measure to utilize it.

DAX Currency Table:

Table Currency =
DISTINCT(
UNION(
SELECTCOLUMNS(SUMMARIZE('Table','Table'[Currency 1]),"Currency",'Table'[Currency 1]),
SELECTCOLUMNS(SUMMARIZE('Table','Table'[Currency 2]),"Currency",'Table'[Currency 2]),
SELECTCOLUMNS(SUMMARIZE('Table','Table'[Currency 3]),"Currency",'Table'[Currency 3])
)
)
Then use this measure:
Value =
var curr = SELECTEDVALUE('Table Currency'[Currency])
var m1 = CALCULATE(sum('Table'[Material value 1]),'Table'[Currency 1]=curr)
var m2 = CALCULATE(sum('Table'[Material value 2]),'Table'[Currency 2]=curr)
var m3 = CALCULATE(sum('Table'[Material value 3]),'Table'[Currency 3]=curr)
var totalm = m1 + m2 + m3
return
totalm
 
No relationship between the tables.