Create a table through DAX formula by combining two tables unique record

[abhishekrws01]

Hello,

I have two tables, forecast & actual, where data is at the country & Product levels. I want to compare how I am doing against the forecast at an aggregate level and by country & product level. It is possible that for a few countries, I don't have a forecast but have actuals vv few countries have forecast but don't have actuals. I am trying to create a 3rd table where I can see all the countries, including ones that have no forecast, but actual vv have forecast but no actuals. The table looks like this.

Forecast table

Country       Product    Sales

Country 1    product 1 100

Country 2    product 2  200

Country 5     product1  100

Actual table

Country       Product    Sales

Country 1    product 1 120

Country 4    product 2  220

Country 3    product 1  200

Desired table

Country       Product    ForecastSales   Actual Sales

Country 1    product 1 100                       120

Country 2    product 2  200

Country 3    product 1                              200

Country 4    product 2                              220

Country 5     product1  100

Please help if possible

Thank you.

[Jihwan_Kim · Accepted solution]

Hi,

Please check the below picture and the attached pbix file.

New Table = 
VAR _country =
    DISTINCT (
        UNION ( DISTINCT ( ForecastSales[Country] ), DISTINCT ( ActualSales[Country] ) )
    )
VAR _product =
    DISTINCT (
        UNION ( DISTINCT ( ForecastSales[Product] ), DISTINCT ( ActualSales[Product] ) )
    )
VAR _countryproduct =
    SELECTCOLUMNS (
        GENERATE ( _country, _product ),
        "@country", [Country],
        "@product", [Product]
    )
RETURN
    FILTER (
        ADDCOLUMNS (
            _countryproduct,
            "@ForecastSales",
                CALCULATE (
                    SUM ( ForecastSales[Sales] ),
                    FILTER (
                        ForecastSales,
                        ForecastSales[Country] = [@country]
                            && ForecastSales[Product] = [@product]
                    )
                ),
            "@ActualSales",
                CALCULATE (
                    SUM ( ActualSales[Sales] ),
                    FILTER (
                        ActualSales,
                        ActualSales[Country] = [@country]
                            && ActualSales[Product] = [@product]
                    )
                )
        ),
        [@ForecastSales] <> 0
            || [@ActualSales] <> 0
    )

[abhishekrws01]

Thank you very much for your help!

[Anonymous]

Hey my friend, 

You can use a table with

merge =

DISTINCT(UNION(
SELECTCOLUMNS(Table_a, "Coluna1", [Expressão1], "Coluna2", [Expressão2], ...),
SELECTCOLUMNS(Table_b, "Coluna1", [Expressão1], "Coluna2", [Expressão2], ...)
))


If you need all lines, just remove the distinct part.

If it solved your problem, give me a kudo!!! ❤️

[abhishekrws01]

Thank you. It gave me the idea to think a bit differently.