Performing a calculation using two data sources with different column names

[taylorpn82]

Hi all, 

Hoping for some help please. 

I've got two separate data sources. These sources have 4 columns in common, a few superflouous columns and then a crucial column which details the time savings. 

What I am trying to achieve is a visualisation that displays the total savings per Project across both data sources. The total for the two data sources for all projects is being displayed rather than the sub total by project that I am hoping for. 

Please see an example below of what I am hoping to achieve and example of the issue that I am having.

I've tried Calculate (Sum('Data Source 1'[Hours Saved])+Sum('Data Source 2'[TechHours]) but as shown above this gives me the total for each line item (not unexpectedly i might add!). 

I've also tried combining the two data sources based on the 4 common columns plus renaming the two Hours column. However,  unfortunately, the data from Data Source 2 is taken from an entirely separate PBI dashboard (Where it is calculated) so I am seemingly unable to rename the TechHours column to match Data Source 1 😞 

I've searched the forum and haven't yet come across a solution. 

Please can anyone advise what DAX code i need to use to achieve the Desired Visualisation as detailed above? 

Thanks in advance for any assistance you can provide.

[Anonymous · Accepted solution]

Hi @taylorpn82 ,

I created a sample pbix file(see the attachment), please check if that is what you want.

1. Create a project dimension table

Projects = 
VAR _tab =
    UNION (
        VALUES ( 'Data Source 1'[Project] ),
        VALUES ( 'Data Source 2'[Project] )
    )
RETURN
    DISTINCT ( FILTER ( _tab, NOT ( ISBLANK ( [Project] ) ) ) )

2. Create two measures as below to get the sum of hours saved

Measure = 
VAR _selproject =
    SELECTEDVALUE ( 'Projects'[Project] )
VAR _ds1 =
    CALCULATE (
        SUM ( 'Data Source 1'[Hours Saved] ),
        FILTER ( 'Data Source 1', 'Data Source 1'[Project] = _selproject )
    )
VAR _ds2 =
    CALCULATE (
        SUM ( 'Data Source 2'[TechHours] ),
        FILTER ( 'Data Source 2', 'Data Source 2'[Project] = _selproject )
    )
RETURN
    _ds1 + _ds2

Total Hours Saved = SUMX(VALUES('Projects'[Project]),[Measure])

3. Create a visual

Best Regards

[taylorpn82]

hi @Anonymous,

Thank you so much for this! This is a very clean and impressive way of achieving this - I really appreciate your time in pulling together such a clear demonstration. 

Thanks again 🙂

[Ashish_Mathur]

Hi,

Create 4 seperate Dim Tables - one each of the common columns that exist in both tables.  Create a Many to One relationship from the common columns of each of the 2 tables to the 4 Dim Tables.  To your visual, drag the common columns from the 4 Dim Tables.  Write this measure

Measure = Sum('Data Source 1'[Hours Saved])+Sum('Data Source 2'[TechHours])

Hope this helps.