Count of related values

[wilson_smyth]

I have two tables that are related in many to many relationship.
This is represented in PowerBI through the fact table.

For each programme i want to get a count of all its modules

For each module i want a count of all its programmes

The difficult part is that, regardless of filters on a table, i want to see the whole count, and no unrelated rows.

e.g.

M4 is related to 2 progrmmes.

In the below image, the value for the measure is correct, but its repeating for ALL programmes, which is incorrect, as M4 is only related to P1 & P5.

Required result is  a table with just 2 rows:

Module      Programme          measure
M4             P1                        2
M4             P5                        2

instead im getting the table in the below image

An example with data is at this link.

Appreciate any help and expertise. Thanks

[Zubair_Muhammad · Accepted solution]

@wilson_smyth

This MEASURE shall do it

Measure =
CALCULATE (
    DISTINCTCOUNT ( Programme[Programme Desc] ),
    ALLEXCEPT ( 'Fact', Module[ModuleDesc] ),
    VALUES ( Module[ModuleDesc] )
)

[Zubair_Muhammad]

@wilson_smyth

Please see your revised file attached here

[wilson_smyth]

Thank you!

To confirm i understand the solution, it does not do a count on the programme table, but instead does a count on the fact table, and ignores the filter on the Module, thus giving granularity at the programme level.

I was looking at it incorrectly and trying to count rows in the dimension table.

This has been very useful and educational, thanks!

[Zubair_Muhammad]

@wilson_smyth

Another way of getting same results is

Measure 2 =
CALCULATE (
    DISTINCTCOUNT ( Programme[Programme Desc] ),
    ALL ( 'Fact' ),
    VALUES ( Module[ModuleDesc] )
)

 Actually when filters are removed from a many side table...effect is passed on to one side table......But not vice versa.