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.

     

     

v-eachen-msft's avatar
v-eachen-msft
Community Support
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.

 

 

  • kbol's avatar
    kbol
    Helper I
    6 years ago

    Hello v-eachen-msft ,

     

    Thank you, that idea works. I am quite surprised that the FILTER expression on Project in my attempt apparently changes the context such that it discards the filtering from Staff on StaffProj, but using that ALLSELECTED idea addresses that issue with this measure:

    Projs With Selected Staff = CALCULATE(
    COUNTROWS(Project),
    FILTER(
        Project,
        CALCULATE(COUNTROWS(StaffProj), ALLSELECTED(Staff))
    )
)

    I've accepted your post as the solution since it is more logical in this scenario. I don't fully understand why we need the ALLSELECTED here, but it does work.