Forum Discussion

Forrestgump's avatar
Forrestgump
Frequent Visitor
8 years ago

Count where the join fields are equal

Hi All,   I have 2 tables in Power BI which are Bi-Directionally linked. The unique Identifiers have a 1 to 1 relationship. In the first table SurveyTable there are 2,532 records in the second tabl...
Forrestgump's avatar
Forrestgump
Frequent Visitor
8 years ago

Hi There,

 

I have a bi-directional join within Power Bi between 2 tables. The headcount table has 23,409 rows of data, the survey data has 2,532. Within the headcount data there is a field called Primary Segment. What I what to do is to do a count of Primary Segments where the join field is equal. I did this within access and the answer was 2,341. I am not sure how to achieve this in powerbi. I thought it might be a calculate function something like:-

 

Calculate(CountRows(SurveyData[ID],Headcount[PrimarySegment])

 

However, no luck. Any help would be appreciated.

 

Kind regards,

 

James Elwell

  • v-danhe-msft's avatar
    v-danhe-msft
    Microsoft Employee
    8 years ago

    Hi Forrestgump,

    Based on my test, you could refer to below steps:

    Sample data:

    Create a measure:

    a = COUNTROWS(FILTER('Surveydata',RELATED(Headcount[primary segments])<>BLANK()))

    Result:

     

    Regards,

    Daniel He