Relationship in the model and output in table and formula

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.

Hi @jmwdba, we aim to consistently improve the topics being posted on the forum to help you in getting a strong solution faster. While waiting for a response, here are some tips so you can get the most out of the forum and other Enterprise DNA resources.

  • Use the forum search to discover if your query has been asked before by another member.
  • When posting a topic with formula make sure that it is correctly formatted to preformated text </>.
  • Use the proper category that best describes your topic
  • Provide as much context to a question as possible.
  • Include demo pbix file, images of the entire scenario you are dealing with, screenshot of the data model, details of how you want to visualize a result, and any other supporting links and details.

I also suggest that you check the forum guideline How To Use The Enterprise DNA Support Forum. Not adhering to it may sometimes cause delay in getting an answer.

Hi @jmwdba

Can you share a sample PBIX to check this.

Hi @jmwdba

would be great if you can shared the PBIX file and your required output.

Would be happy to cooperate :slight_smile:

Regards

Hi @jmwdba, we’ve noticed that no response has been received from you since the 3rd of June. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the checkbox. Thanks!