Forum Discussion

AndyTrezise's avatar
AndyTrezise
Advocate IV
8 years ago

Using CALCULATE on unrelated table

Hi All   I am attempting to display summary lines in a PBI table for projects (these are pulled out of a simple sharepoint list) and then also include associated data from other tables held in our ...
Need Help
Stachu's avatar
Stachu
Community Champion
8 years ago

first of all I would chagne the data model:

1) add calendar table

2) create project list table with unique projects only

3) change the month granlarity to 1st day of the month in Projects table

4) optional - aggregate 'associated' tables into 1 in Power Query

 

then I would join the 1 & 2 with Projects and either aggregated data or individual tables

as long as Prj Code, Year, Month would come from tables 1&2 they will propagate filter context to underlying data tables, being able to produce the output you expect

  • AndyTrezise's avatar
    AndyTrezise
    Advocate IV
    8 years ago

    Hi - Thanks for the reply

     

    I've tried your suggestion by creating a calendar and project master table to drive the data however I am still struggling to get the report slicers to work as I'd expect. I think it may be something to do with the measure I use on the table.

     

    To try and make it clearer - here is my data model.

     

     

    TimeByDay in Prj_Status table is set to 1st day of month. It is linked to the Calendar on this column and also to Prj_Master on ParentProject column. There will potentially be a row for each combination of project/year/month.

     

    Assignments has thousands of rows for all projects (not all projects are in the Prj_Status table) and each row has a ParentProject and TimeByDay which is how it is joined to the two driving tables (Prj_Master and Calendar).

     

    I have created an independent Measures Table to hold calculated measures that I want to show revenue (which is held in Assignments) and need it to be summarised by project/year/month.

     

    So...in this example I have 4 rows in my Prj_Status table Jan-Mar for the first project and April for the second.

     

    All the data in the matrix is derived from Prj_Status except the measures which is a SUMX of revenue in assignments.

     

    ThisMonth + 0 should show revenue by Project / Year / Month number

    ThisMonth + 1 shoud show revenue for next month and ThisMonth + 1 should show revenue for two months ahead.

     

    Instead these columns are showing all revenue for both projects for all periods.

     

    I have tried various CALCULATE DAX() formula with FILTERS etc. but can't find a way to get this to work.

    • Stachu's avatar
      Stachu
      Community Champion
      8 years ago

      what formulas do you have for the measures?
      From what I see the syntax should be quite simple, like:

      'This Month + 0' = SUM('Assigments[Value])

      maybe wrapping it into calculate to add filter on revenue/forecast etc.

      • AndyTrezise's avatar
        AndyTrezise
        Advocate IV
        8 years ago

        I first calculate revenue as a column on the Assignments table:

         

        Revenue = Assignments[AssignmentWork]*Assignments[Tasks.Probability]*Assignments[Projects.HourlyRate]

         

        My measure is then simply:

         

        ThisMonth + 0 = SUM(Assignments[Revenue])

         

        With the measures added to the table neither of the two slicers have any effect. I have to remove the measures before the filtering works. It's really weird.

         

        There is no direct link in my visual table between the columns (all of which come from Prj_Status) and my measures (which are calculated from assignments).