Forum Discussion

kbol's avatar
kbol
Helper I
6 years ago
Solved

Count on Dim Table Based on Fact Table Filtering

Hello, I've got a model like this:   My goal is to count the number of rows in the Projects table that have at least 1 corresponding row in the StaffProj for a given selection of Staff. My a...
Need Help
  • v-eachen-msft's avatar
    v-eachen-msft
    6 years ago

    Hi kbol ,

     

    You could try the following measure.

    Projs With Selected Staff =
CALCULATE (
    DISTINCTCOUNT ( 'StaffProj'[Project] ),
    ALLSELECTED ( 'Staff'[Name] )
)

    Here is the test result.

     

     

Greg_Deckler's avatar
Greg_Deckler
Community Champion
6 years ago

Perhaps a column in Project table like:

 

Count = COUNTROWS(RELATEDTABLE('StaffProj')

 

Then you could create a measure:

 

Project Count = COUNTROWS(FILTER('Projects'),[Count] > 0)

  • kbol's avatar
    kbol
    Helper I
    6 years ago

    Hi,

    I'm afraid that Project column solution doesn't quite work because the filtering on Staff can change and the column is computed at refresh time, including all Staff. Thus with the above data is evaluates to 6 for every project, each having two associated staff.