Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends September 15. Request your voucher.

Reply
wilson_smyth
Post Patron
Post Patron

Count of related values

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

 

Capture33.JPG

 

 

An example with data is at this link.

Appreciate any help and expertise. Thanks

1 ACCEPTED SOLUTION
Zubair_Muhammad
Community Champion
Community Champion

@wilson_smyth

 

This MEASURE shall do it

 

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

View solution in original post

4 REPLIES 4
Zubair_Muhammad
Community Champion
Community Champion

@wilson_smyth

 

This MEASURE shall do it

 

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

@wilson_smyth

 

Please see your revised file attached here

 

example1.png

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!

@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.

 

 

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

Find out what's new and trending in the Fabric community.