0

I have two sets of identical data that I filter differently. One shows sales by location in test locations and the other in control locations. Is there a way to append the results in a table with a "Test/Control" flag based on the first set of slicers so that I can show all the locations color coded by the flag?

a.s.1
  • 39
  • 7

1 Answers1

0

You have two options to achieve this. In the Model (DAX) you can create a calculated Table, and use the UNION function to append the two sets of rows together.

https://dax.guide/union/

However UNION is quite fussy - the two parameter tables must have the same set of columns. Sometimes you can overcome small differences by adding other functions, but complex transforms are harder and you cant debug.

For complex requirements, you can use the Power Query Editor - it has an Append Query button on the Home Ribbon. Each query you feed in can have complex transformations.

Mike Honey
  • 14,523
  • 1
  • 24
  • 40
  • I've tried to append the two tables in PQE but it doesn't seem to append based on the slicers, it merely adds all the rows together. So if table one has every location filtered by slicer 1 and table 2 is filtered by slicer 2 will the appended table contain only the filtered locations from both tables? – a.s.1 Mar 24 '21 at 02:04
  • You would need to prepare the two input queries so there is a "Location" column in both sets of rows. Then you can build a slicer on that. – Mike Honey Mar 24 '21 at 06:55