SUMMERIZE with FILTER and Union

[Anonymous]

Hi, I have millions for records in Sales table and we never use these Transaction level data in these reports. Always aggregate data.

Step 1: So I've created a Aggregate table using SUMMERIZE function and its working, seeing data, and report also looks good.

step 2. Added a filter using CalculateTable(): and this is also working, seeing data on the report as expected.

           CALCULATETABLE ( SUMMERIZE (SalesFact,......), FILTER(SalesFact,...))

Step 3. I have another table and am trying to combine this table into above summerize table. So, am using Union as below:

          UNION( CALCULATETABLE ( SUMMERIZE (SalesFact,......), FILTER(SalesFact,...)) , SUMMERIZE(SalesFactTable2,...)

When am done with creating this new table, am seeing data in the Data view. But am not able to see any data on the report.

and also I completely created new page to test, just pulled Total Sales onto the report and no data. but when I remove UNION part in the above DAX it's working.

Really appreciate help.

[v-xicai · Accepted solution]

Hi @Anonymous ,

You may considerate the limitations of UNION function:

•     The two tables must have the same number of columns.
•     Columns are combined by position in their respective tables.
•     The column names in the return table will match the column names in table_expression1.
•     Duplicate rows are retained.
•     The columns must also have similar data types

By my test using the functions and formula above to reproduce your scenario, it works well. You may upgrade the Power BI Desktop to the latest version 2.74.5619.862, and try it again.

You can download my test pbix from here.

Best Regards,

Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thank you, @v-xicai  based on your suggestion I've created a view in the database outside Power BI for summerized data (from both tables) and using this view as Source. Now I just have 5000 rows from 20 million rows.

For these reports I dont need indiivdual transactions.