Projects missing

[Sudhavi_84]

Hi Experts,

Just thought to explain clearly in case if it is not clear if my previous comments were not clear.

I have "Master" Table which includes Parent and Child Project in same column. Example Gov10.A123.23 and A123.23

I have MStone table which includes Child Project only. However when you see A123.23  Project that means it is coming from Gov10.A123.23.

I have Fact Table which includes separate values for Gov10.A123.23 and A123.23 from the same column.

I am trying to sum the Values which has in Fact Table from MStone table based on Project name, Project level dates using related "Master" Table

My current issue is I am getting values for Projects which are matching in "MStone" table only (With this Parent Projects from Master or Fact are not appearing) . I want to get consider and retrieve the related Parent  projects for Child Projects in Mstone Table

Please see the PBIX Model and desired output I am expecting.

https://1drv.ms/u/s!An2H1J0iSc93a9rkwrkLRgCEwYc?e=SXs2Qd

 This is the actual measure I am using 

My Measure =

VAR XYZMaxDate = CALCULATE(MAX(Mstone[Project Level Date]),Mstone[Project Level] = "XYZ",Mstone[Desc]="Current")

VAR ABCMaxDate = CALCULATE(MAX(Mstone[Project Level Date]),Mstone[Project Level] = "ABC",Mstone[Desc]="Current")

var Prj = SELECTEDVALUE(Mstone[Project])

VAR TOT= CALCULATE(SUM(Fact[Cost]),FILTER('Fact','Fact'[Per] >=XYZMaxDate && 'Fact'[Per]<=ABCMaxDate),Master[Projects]=Prj,Fact[Desc]="Current")

Return TOT

I am getting output as

But I am expecting it need to come as below to include bottom 2 Parent projects as well please.

Thank you very much for looking into it.

[MFelix]

Hi @Sudhavi_84 ,

The issue in your data is that the projects GovA123.23 and GovA123.24 are not part of the MStone table so in your calculation you are not picking up any values for them since they are not refered in the milestones and not getting any values.

Altough I have a question regarding your calculation you have the two variable below:

VAR XYZMaxDate = CALCULATE(MAX(Mstone[Project Level Date]),Mstone[Project Level] = "XYZ",Mstone[Desc]="Current")
VAR ABCMaxDate = CALCULATE(MAX(Mstone[Project Level Date]),Mstone[Project Level] = "ABC",Mstone[Desc]="Current")

What are the values you want to pickup on this two measures is it the values for those specific level projects XYZ, in this case how are you getting the calculation for the GOV projects?

Believe that this type of calculation needs to have the parent child hierarchy to get the correct calculation done:

https://www.daxpatterns.com/parent-child-hierarchies/

[Sudhavi_84]

Yeah that is the issue here I can't get the dates for GOV because this GOV projects are not in MStone Table. I agree your point.

Just trying to understand that is there any other way possible or not.

Currenlty it is finding the dates in Mstone table based on Project column from below Master Table.

For Example If Level1 and Level2 are both are same then Look for Level 2 projects in MStone table and then find the dates and do calculation. If Level1 and Level2 are not same then take the same date limit for Level1 and Level2 project and look for Level 1 and Level 2 projects in Fact table and calculate value.

In this way we can use the same dates.

[Anonymous]

Hi @Sudhavi_84 ,

Looks like your formula has filtered the parent level.

You will find that the measure get blank value if you click the 'show item with no data feature'.

Best Regards,

Jay