CODEWITHSUNDEEP
powerbidaxdata-modeling
kanderson
kanderson

Reputation: 177

Showing Side by Side Measures from Semi-Related Tables in Power BI

I have a data model in Power BI that, among other things, has the following tables

The table is modeled like so (simplified version): Data Model

In Power BI, I am trying to make a simplified table view that contains measures based on both the budget history as well as the employee history for the most recent day in the dataset (simple count rows/distinct count of calendar table)

However, attempting to do so gives me the results below if I try to put both measures on the table. Basically it appears to be doing a cross join between each table and matching associates with jobs they don't have (this happens when the budget is added).

enter image description here

Of course, if I just do one of the singular measures everything works perfectly. I am fairly certain it is because there is no real connection between the 'employee' and the 'budget history' in this relationship, so it is just joining everything on the date without any context.

I have tried several things such as making inactive relationships with userelationship(), using visual level filters etc. but I'm not sure what the best option in this situation would be. (I am trying to avoid bidirectional relationships if possible)

Ideally this information should show on this date that Joe was present as President, Sally was present as an operator, and the Manager position had nobody, but all three were budgeted.

Any advice is appreciated. I have attached a simplified mockup pbix file for reference.

PBIX File

Upvotes: 1

Views: 324

Answers (1)

RADO
RADO

Reputation: 8148

This is a complicated problem for many reasons. I was able to produce this report:

enter image description here

by removing field "Name" from the table and replacing it a measure:

Employee Name = 
CALCULATE(
    SELECTEDVALUE(Employees[Name]),
    CROSSFILTER(Employees[Employee_ID], Employee_History[Employee_ID], BOTH)
)

It looks exactly like the report you want, but if you have additional requirements, you'll need to make sure that such approach works for you.

If this is acceptable, a brief explanation:

  • the root cause of the issue is missing Employee-Budget relationship. When you put Name in the table as a filter, it doesn't propagate to the budget table and causes a cartesian product.
  • Removing Name from the table eliminates the need for the filter propagation, but then you won't see employee names. I solved this by pulling employee names with the measure, where required propagation is forced by CROSSFILTER function (essentially, it's like a temporary bi-directional relation only when you need it, so it does not negatively affect the rest of the model).

Upvotes: 1

Related Questions