cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
coreyweiss Member
Member

Splice data from 2 tables

 

 Hi there!

 

I have 2 tables that have a quantity and a cost. Ideally, they would be in one table, but the calculations are too complex to have them combined.

 

In this simplified example, I have 2 tables, 

 

Table Cats  
DateCat AmountCat Cost
6/30/2018432
7/1/2018434
7/2/2018444
7/3/2018322
7/4/2018635
7/5/2018221
7/6/2018445

 

Table Dogs 
DateDog AmountDog Cost
6/30/20183987
7/1/2018476
7/2/2018578
7/3/2018477
7/4/201816788
7/5/20188776
7/6/2018488

 

I would like to have one bar chart showing sales per day. The result would be spliced by either cat OR dog, depending what was chosen on the splicer. How can I achieve this with a dax measure? Note, I cannot merge queries.

 

 Any help is much appreciated!

 

Cheers,

 

Corey 

 

1 ACCEPTED SOLUTION

Accepted Solutions
coreyweiss Member
Member

Re: Splice data from 2 tables

Found a way!

 

93.PNG94.PNG95.PNG

 

 

96.PNG

3 REPLIES 3
Community Support Team
Community Support Team

Re: Splice data from 2 tables

Hi @coreyweiss

Please look at my test, steps are below

In query editor

1.Merge queries as new

5.png

2.in the Merge 1 table, expand columns from table Dogs

6.png

3. add two custom columns to calculate sales for Dogs and Cats

7.png

4.select the two custom columns "Sales for Cat" and "Sales for Dog", then select "unpivot only selected columns"

8.png

5.add a conditional column to define which categoary each value belong to, Dog or Cat.

9.png

6.add columns in the chart 

10.png

 

Best Regards

Maggie

coreyweiss Member
Member

Re: Splice data from 2 tables

Thanks for your reply Maggie,

 

I was hoping to do it without merging queries since I have many calculations that would not work in a merged query.

 

I appreciate your time!

 

There may not be a way...

coreyweiss Member
Member

Re: Splice data from 2 tables

Found a way!

 

93.PNG94.PNG95.PNG

 

 

96.PNG