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

Anonymous's avatar
Anonymous
Not applicable
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

taylorpn82's avatar
taylorpn82
Frequent Visitor
2 years ago

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 🙂