Forum Discussion
abhishekrws01
Helper I
3 years agoCreate a table through DAX formula by combining two tables unique record
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...
- 3 years ago
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 )
Anonymous
3 years agoNot applicable
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
Helper I
3 years agoThank you. It gave me the idea to think a bit differently.