I'm fairly new to PBI, so hopefully this is possible, I just can't wrap my head around it.
I have a single report with many different visuals with data from at least 2 tables, and trying to use slicers to filter the data from multiple tables, but I can't quite get it to calculate correctly. I tried to attach a sample pbix file but it won't let me for some reason (how do I attach? I've seen it in other posts), so below are some screenshots of the various scenarios using 2 slicers from the same table/different tables.
Notice that when using the Period slicer from the employee table (right side) I don't get all the "Active Buildings".
When I use the Period slicer from the building table (left side), the HC totals don't add up correctly. Really trying not to have multiple "Period" slicers
First dataset is basically a report by building, with various totals (Total seats, headcount, etc...), but it's actually several different reports stacked in a single list so I can trend the data, so I created a unique key by concatenating building code and the period (equivalent to month).
Second data set is a list of employees, which includes their assigned building, so I also trended by creating the same PK (building code + Period/month#), and consolidating multiple months into a single data set.
I also have a few measures created to summarize the employee data that I'm using as the Dynamic values in the text boxes from the first screenshot.
So in both reports I have the Period field (2023P01) and the concatenated field (BLDG01_2023P01) as well as a GEO field where the values are the same in each report, which is also a slicer that's not totalling the employee data report if the slicer was created using the Building based dataset, but I think it's all for the same reason, that not all buildings exist in both tables. There are some locations that don't have any headcount assigned, so those locations don't appear in the employee based report, but are included in the building based report to keep track of all buildings in the portfolio. Vice versa, some employees are designated in the building table as "Remote", so same field, but the "Remote" value doesnt' exist in the dataset I'm using, and trying to avoid having to manually add it, as this report will have future periods/months added to the dataset, and I'm trying to simplify the process so as to not have to add placeholder rows each time.
I have a dashboard page with various visuals but the ones that are giving me issues are the Text boxes with dynamic values which I've replicated in the sample data. I have several slicers with data that is the same in both datasets; Period (2023P01, 2023P02, etc..) and Portfolio/GEO (AMS, EMEA, APJ).
One of the Text boxes w/ dynamic text (basically a scorecard that I created myself, due to the text not wrapping in the actual scorecard) has # of Active Sites, and the other is various headcount totals (Remote HC, FTE headcount, etc..)
If my Period Slicer is using the Period field from the employee based report, the # of active sites is too low, since as I mentioned that report only has buildings which have headcount.
So if I use the period field from the building based report, then I get the correct Number of Active sites, but my some of the other text box w/ dynamic values for headcount totals just blanks out, I think because the buildings don't exist in the buildings table? you can see this in the 2 scenarios on the left of the sample data report, since theyre using bldg table for period slicers, but the measures are counting the the records in the employees table. The box for Remote headcount blanks out when a bldg slicer is used, I think because "REMOTE" doesnt' exist in the buildings table.
How do I get the slicers to work since the data it's slicing exists in both tables and is the same? I'm pretty sure it's because the building table doesn't have Remote in it, and vice versa the employee table doesn't have all the buildings that have 0 headcount but are still active, but I'm not sure, and if is, I can't figure out how to resolve it short of adding Remote to the buildings table, and the missing 0 Headcount buildings to the employee table? Although not sure how I'd do the latter.
Sorry for the long post but been trying to figure this out for a weeks now and nothing's working.
Could someone help with what I'm doing wrong with my post? Haven't received a single reply, and I thought I provided plenty of detail (maybe too much?) and provided examples of sample data since this site apparently won't allow me to attach a pbix file unless I'm a super user.
I'm guessing it's something I just dont understand about how the table relationships work, but I just can't figure it out. for some reason this isn't working when it seems like it should.
