Using filter and relationships to produce the right aggregation.

[Anonymous]

I'm starting to learn DAX and have a very simple question:

I have two tables: Project and Project Status

1. I imported the table to Power BI and created a 1-M relationship between Project and Project Status.
   
   
2. I created a count measure Count([ProjectId]) under the 'Project' and 'Project Status' queries called 'Project Count From Project' and 'Project Count from Project Status' respectively. The formula is the same.
   
   
3. I setup a test visualisation that would give the number of projects for each project status.
   
   
4. However the measure 'Project Count From Project' gives me the total count of projects and doesn't take into account the row context. 
   
   
   
5. My intention is to create a measure which will produce the correct result independent of the context. I would like to avoid having to create separate measures that have the same purpose. For example, there could be another table called ProjectMembers. It doesn't make sense to create another measure just to show the number of projects each member belongs to. 
   
   
6. I'm very keen to understand why 'Project Count from Project' returns all the rows and ignores the project status context.

[daxer-almighty · Accepted solution]

Since the default filtering direction is from the one-side to the many-side and not the other way round (unless you switch on the bi-directional filtering but YOU SHOULD NOT DO SO), if you put a filter on a column in the many-side table, the filter will NOT propagate to the one-side table. Hence, counting rows in the one-side table will return 3, that is, all the rows in the table (since no filtering occurs). The rule of dimensional modeling, which is the basis for Power BI, is that you should only select attributes from dimensions (the one-side table) and keep fact tables (the many-side) hidden and never select anything from it (with very, very, very few exceptions).

This also means that you should have a separate dimension with statuses and connect it to the fact table. Then you can select projects from the Projects dimension and statuses from the Statuses dimension. Your measure that will then calculate correctly (regardless of anything) the number of different projects with the selected statuses is:

[# Projects] = distinctcount( 'Project Status'[ProjectId] )

Easy.

Please remember that 'Project Status' is a fact table and must be hidden (you should not select from it). Regarding the formula that @wdx223_Daniel gave you... Yes, it'll work but it's very inefficient and such techniques (his one uses the Expanded Tables Theory) should be avoided, especially with big fact tables. Just try to run his formula on a really big fact table and you'll see why.

[wdx223_Daniel]

@Anonymous try this code

=calculate (distinctcount (project[projectid]),'project status')