Is it possible to shows data from two tables to a single Chart? (Using both Y axis)

[thomsontam]

Hi All,

I had the follow data table:

table1 sample:

Store	Customers	Date
A	50	01 June 2020
B	30	01 June 2020
C	80	01 June 2020
A	55	02 June 2020
B	31	02 June 2020
C	90	02 June 2020

Table 2 Sample:

Store	Product	Quality	Date
A	Coffee	40	01 June 2020
A	Cake	20	01 June 2020
A	Tea	10	01 June 2020
A	Coffee	38	02 June 2020
A	Cake	25	02 June 2020
A	Tea	17	02 June 2020
B	Coffee	20	01 June 2020
B	Cake	2	01 June 2020
B	Tea	10	01 June 2020
B	Coffee	25	02 June 2020
B	Cake	5	02 June 2020
B	Tea	6	02 June 2020
C	Coffee	60	01 June 2020
C	Cake	30	01 June 2020
C	Tea	20	01 June 2020
C	Coffee	55	02 June 2020
C	Cake	40	02 June 2020
C	Tea	25	02 June 2020

We can create a report in Excel by merger those data together and put products on Primary Y asix and Total Customers on Seconary Y asix. However, I am unable to find option to do it in PowerBI.  Currently I create two charts overlay together to get the same result as Excel.  Is it possble to replicate it in a single chart under PowerBI?

Thanks for your help!

[Icey · Accepted solution]

Hi @thomsontam ,

It is suggested to create a Dates table and a Stores table. Then create relationships among the tables.

Dates = 
CALENDAR (
    MIN ( MIN ( 'Table 1'[Date] ), MIN ( 'Table 2'[Date] ) ),
    MAX ( MAX ( 'Table 1'[Date] ), MAX ( 'Table 2'[Date] ) )
)

Stores =
DISTINCT (
    UNION ( DISTINCT ( 'Table 1'[Store] ), DISTINCT ( 'Table 2'[Store] ) )
)

Or, you can use Merge to combine the two tables to one.

BTW, .pbix file attached.

Best Regards,

Icey

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

[amitchandak]

@thomsontam , Create a common dimension of store and Date and you can analyze data together

Store Dim = DISTINCT(UNION(ALL('Table 1'[Store ]),ALL('Table 2'[Store ])))

// You an use only one table

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...


Appreciate your Kudos.