topic Re: Create a table through DAX formula by combining two tables unique record in DAX Commands and Tips

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

abhishekrws01 — Fri, 17 Mar 2023 15:13:14 GMT

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.

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

Anonymous — Fri, 17 Mar 2023 15:42:47 GMT

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!!! ❤️

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

Jihwan_Kim — Sun, 19 Mar 2023 05:43:16 GMT

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 )

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

abhishekrws01 — Mon, 20 Mar 2023 12:18:57 GMT

Thank you very much for your help!

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

abhishekrws01 — Mon, 20 Mar 2023 12:21:05 GMT

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