So what I am doing is taking data from my ‘Registrations’ table and marrying it with information from the ‘Projections’ table to create a table matrix.
Everything works until I attempt to bring in the Military or Civilian Column. I have established an inactive relationship between these items in my data model and try a couple of different things in the formula to make this work together.
This is the core formula that I need to figure out how I need to adjust so that the filter logic works.
> Projections =
>
> VAR TotalUGCiv =
>
> CALCULATE( SUM(projections[Projection]),
>
> projections[Projection Type] = "UGRD/Civilian",
>
> FILTER(projections, projections[Projection Type] = "UGRD/Civilian"), VALUES( Dates[Academic Year]), VALUES( 'Worldwide Locations'[Location Group]), VALUES( 'Worldwide Locations'[Location Code])
>
> )
>
> VAR TotalUGMil =
>
> CALCULATE( SUM(projections[Projection]),
>
> projections[Projection Type] = "UGRD/Military",
>
> FILTER(projections, projections[Projection Type] = "UGRD/Military"), VALUES( Dates[Academic Year]), VALUES( 'Worldwide Locations'[Location Group]), VALUES( 'Worldwide Locations'[Location Code])
>
> )
>
> VAR TotalGRCiv =
>
> CALCULATE( SUM(projections[Projection]),
>
> projections[Projection Type] = "GRAD/Civilian",
>
> FILTER(projections, projections[Projection Type] = "GRAD/Civilian"), VALUES( Dates[Academic Year]), VALUES( 'Worldwide Locations'[Location Group]), VALUES( 'Worldwide Locations'[Location Code])
>
> )
>
> VAR TotalGRMil =
>
> CALCULATE( SUM(projections[Projection]),
>
> projections[Projection Type] = "GRAD/Military",
>
> FILTER(projections, projections[Projection Type] = "GRAD/Military"), VALUES( Dates[Academic Year]), VALUES( 'Worldwide Locations'[Location Group]), VALUES( 'Worldwide Locations'[Location Code])
>
> )
>
> RETURN
CALCULATE(TotalUGCiv + TotalUGMil + TotalGRCiv + TotalGRMil, VALUES( Dates[Academic Year]), VALUES( ‘Worldwide Locations’[Location Group]), VALUES( ‘Worldwide Locations’[Location Code]), USERELATIONSHIP(projections[Military or Civilian], ‘Worldwide Registrations’[Academic Year End Military or Civilian] ))
If it were working properly then Projections under Civilian should not show the same number as Military Under Projection. They have their own values in the data table within the model. Everything works as it should until I add the Military or Civilian Column below Undergraduate or Graduate. Tuition Revenue, Registrations are correct they come from the Registrations table and everything else comes from the Projections table.
What am I missing? Both tables have the Military or Civilian column in them and the values are the same thats why I have the connection between Projections and Worldwide Registrations based on that column.
