Search code examples
powerbidaxpowerbi-desktopmeasure

Power BI, issue on sum of several tables with two conditions

I struggle to find a way to sum columns of severals tables into one measure with two filters. To make it clearer, I have three tables as input (they have other columns that I hide as they have no purpose for this use case):

Table 1

Date        Currency    Amount
01/01/2020  USD         100
01/01/2020  EUR         50
02/01/2020  USD         200

Table 2

Date        Currency    Amount
01/01/2020  USD         200
02/01/2020  USD         100
02/01/2020  EUR         100

Table 3

Date        Currency    Amount
01/01/2020  USD         50
01/01/2020  EUR         50
03/01/2020  EUR         100

I have no difficulties to do with one filter like date but I can't find a solution with two filters in order to have in results, a table in my dashboard that would display this:

Date        Currency    Amount
01/01/2020  USD         350
01/01/2020  EUR         100
02/01/2020  USD         300
02/01/2020  EUR         100
03/01/2020  EUR         100

I don’t find any solution, should I use a new table and aggregate columns in it? My three tables are linked by a relation on date but I can’t add a relation for the Currency. I’m a bit lost with what to do in Power Bi with this use case..

Thanks in advance,

Solution

  • You can create a custom table using table 1, 2 and 3 as below. Finally you can create your necessary Measures in the new "Custom_table" as per your requirement.

    custom_table = 
UNION(
    SELECTCOLUMNS(
        table1,
        "Date",table1[Date],
        "Currency",table1[Currency],
        "Amount",table1[Amount]
    ),
    SELECTCOLUMNS(
       table2,
        "Date",table2[Date],
        "Currency",table2[Currency],
        "Amount",table2[Amount]
    ),
    SELECTCOLUMNS(
        table3,
        "Date",table3[Date],
        "Currency",table3[Currency],
    "Amount",table3[Amount]
    )
)