Joining 2 tables to create a single stacked bar chart

[mike_asplin]

I know I could do this in power query , but model is already quite large and refreshes fail so reluctant to add more to it. So is it possible to do this in DAX 

Top left is a table of 8 different contact types that are digital. On the right the call log.  I can create  stacked bar chart for first table just dropping the [contact Type] on the ledgend field and adding a row count to the values. 

is it possible to create calculate table that appends the call table with the digital table so the calls woudl have contact type = "Call In" and still be able to drop the contact type on the legend?  I know typically you cant use calcuated values on rows/column or axes of graphs.

Thanks for any advice

[Ray_Minds]

Answer :
Create a calculated table using this DAX

Combined Contact Log =
UNION (
    SELECTCOLUMNS (
        'DigitalContacts',
        "ContactType", 'DigitalContacts'[Contact Type],
        "ContactDate", 'DigitalContacts'[Contact Date],
        "Client", 'DigitalContacts'[ClientName]
    ),
    SELECTCOLUMNS (
        'Calls',
        "ContactType", "Call In",
        "ContactDate", 'Calls'[JoinedDate],
        "Client", 'Calls'[Client]
    )
)

Step2:  Use this new table in visuals

Now you can:

• Use ContactType on the Legend
• Use ContactDate on the Axis
• Use Count of ContactType or Count of rows on Values

This will give you a single stacked bar (or column) chart that includes both digital contact types and calls together.

Output:
 

[mike_asplin]

Perfect worked like a dream thanks so much

[mike_asplin]

got it knew there woudl be a way without duplicating. I'll give it a go now

[v-pnaroju-msft]

Thankyou, @MarkLaf for your response.

Hi mike_asplin,

We appreciate your inquiry through the Microsoft Fabric Community Forum.

We would like to inquire whether have you got the chance to check the solution provided by @MarkLaf  to resolve the issue. We hope the information provided helps to clear the query. Should you have any further queries, kindly feel free to contact the Microsoft Fabric community.

Thank you.

[MarkLaf · Accepted solution]

Rather than combine the tables into a single table and duplicating your data. I would leave it in your model as two separate fact tables. The trick for what you want to do would be to create a 'Contact Types' calculated dimension table and use that in your axis.

Here is a simple example that mimics your data.

Starting tables:

Digital

AdviserId	Contact Date	Contact Type	ClientName
1	1/1/2025	Start	ClientA
1	1/2/2025	Process	ClientA
1	1/3/2025	Process	ClientA
1	1/4/2025	End	ClientA
2	1/1/2025	Start	ClientB
2	1/2/2025	Process	ClientB
3	1/1/2025	Start	ClientA
3	1/2/2025	Process	ClientA
3	1/3/2025	Process	ClientA

Calls

AdviserID	JoinedDate	Client
4	1/1/2025	ClientA
4	1/2/2025	ClientA
4	1/3/2025	ClientA
5	1/4/2025	ClientC
5	1/5/2025	ClientC
5	1/6/2025	ClientC
6	1/4/2025	ClientD
6	1/5/2025	ClientD
6	1/6/2025	ClientD

Dates

Dates = CALENDARAUTO()

Note: I'm assuming you have a separate Dates table. Be sure to add one to your model if you haven't already. I don't need anything more than the most barebones for this example, but check out here for adding Dates with a bit more metadata: https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

With tables like the above, follow these steps:

1) Add a [Contact Type] calculated column to 'Calls'

Contact Type = "Call In"

2) Create a combined 'Contact Types' calculated table

Contact Types = UNION( DISTINCT( Digital[Contact Type] ), DISTINCT( Calls[Contact Type] ) )

3) Now make sure your dimension tables (in this simple example, they are now 'Dates' and 'Contact Types') are related 1:* to your fact tables ('Digital' and 'Calls'). The model would look like:

4) Create a measure that gives your the combined count of Digitcal + Calls.

Digital + Calls = COUNTROWS( Digital ) + COUNTROWS( Calls )

5) Now you can drop these elements into your stacked bar chart.

• X-axis: Dates[Date]
• Y-axis: [Digital + Calls]
• Legend: 'Contact Types'[Contact Type]