0

I have a PowerPivot with two tables one contains a list of facilities, their type (active/inactive) and whether they belong to org A or org B (FaciltyID|Active/Inactive|ORG)

Another table has a list of users and facitilites assigned to them + their org, so it looks like (userID|FacilityID|ORG) where each userID is repeated the number of times that=the number of facilties it has.

Initially I needed to report the number of facilities active and easily built a PivotTable for it.

Now I need to get a list of the facilities that each user is missing , so I need to basically do an outer join between the the tables for each user and I just can't figure out the way to do it! I joined both table on the FacilityID and am able to see whether they have inactive facilties, but can't figure out a way to show all the facilities they are missing!

Thanks

Mike
  • 39
  • 1
  • 5

1 Answers1

0

Nonexistence is hard. This is not the sort of thing that is best solved through measures, but through modeling. In your source, you should cross join Facility and User to get FacilityUser. In FacilityUser, every user has 1 row with every facility, and you add a flag to indicate whether the user is or isn't assigned to that facility. Then your problem becomes one of filtering on that flag value. This is solvable in DAX, but you don't want to do that.

greggyb
  • 3,728
  • 1
  • 11
  • 32
  • Thanks, I did that and took a dive in the dax documentation too. Now just trying to figure out the best way to display the data – Mike Dec 01 '15 at 22:51