Forum Discussion

taylorpn82's avatar
taylorpn82
Frequent Visitor
2 years ago
Solved

Performing a calculation using two data sources with different column names

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 sav...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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

Ashish_Mathur's avatar
Ashish_Mathur
Super User
2 years ago

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.