Same measure fails to produce correct aggregation.

[Anonymous]

I have imported two tables into Power BI and created a 1:M relationship between Project and Project Status.

I created two measures containing the expression COUNT([ProjectId]) under the Project and ProjectStatus queries called 'Project Count From Project' and 'Project Count From Project Status'.

However the result 'Project Count From Project' shows all the rows. In my scenario how can I create one measure which will give me the correct result whether I'm counting number of projects by status or number of projects a project member participates in.

I'm very keen to understand why the 'Project Count From Project' measure doesn't work. It would be nice not to have to create different measures which are used for the same purpose, ie, to count the number of projects according to the context filter.

[Anonymous]

The golden rule of data modeling says that models should be dimensional. It means you should follow these practices: https://docs.microsoft.com/en-us/power-bi/guidance/star-schema. And you should keep your fact tables hidden, only dimensions sholud be used for slicing and dicing.

[Anonymous]

I'm with you, daxer. Unfortunately I'm not allowed to implement a typical star-schema model. I have to produce Power Bi reports off a fully normalised OLTP database designed for updates not for reporting. Tough.

[AlB]

Hi @Anonymous 

You are placing ProjectStatus[Status] on the rows of the visual. That determines your filter context. The filtering propagates from the Project to the ProjectStatus table, not otherwise. Since the Project table is not affected by the filter context COUNT(Project[ProjectID]) returns the number of rows in the unfiltered table, i.e., 3

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[Anonymous]

So the measure 'Project Count From Project Status' works because the ProjectId is coming Project Status?

Project Count From Project Status = COUNT([ProjectId])

I thought measures were table agnostic? 

So if a add another table called ProjectMember, would I have to code another measure to calculate the number of projects a person belongs to?
Isn't there a way to craft one measure to calculate the number of projects which would show the correct count whether you're looking at project status or project members?

[Payeras_BI]

Hi @Anonymous ,

This is how the propagation of filters works, the Cross filter direction is set to single.

You could wrap your Count measure from the Project table with a Calculate and use the Crossfilter function to change, just for this particular Count, the direction to Both.

Project Count From Project =

CALCULATE(
COUNT(Project[ProjectId]);
CROSSFILTER(Project[ProjectId];ProjectStatus[ProjectID];Both)
)

Regards,

J. Payeras

[Anonymous]

Thank you, J. Payeras for your reply.

Your solution would work, however I'm after a solution that can give me one measure that would work across the board. In my post I mentioned the need to do the same thing for ProjectMembers query. Does that mean I would have to create another ProjectCount measure to work ProjectMembers, and then another ProjectCount measure to work with...hmmm

Perhaps my problem is that my data model doesn't follow a typical star schema model, instead it's just transactional normalised model.
Given my limited knowledge of DAX, the only option to get away with only one measure for ProjectCount is by changing relationship to bidirectional - I know that comes with drawbacks.