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 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.

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

3 Replies

  • 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.

  • 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 🙂