Forum Discussion

byr10112's avatar
byr10112
Icon for Microsoft Employee rankMicrosoft Employee
4 years ago
Solved

Count Occurrence of Column Values in Another Table

This sounds like a pretty simple request, but I havent been able to find a solution yet. Maybe I'm still thinking from a t-SQL perspective.    I'm trying to find how many times a value in a column ...
Need Help
  • sevenhills's avatar
    sevenhills
    4 years ago
    Measure = 
var _a = SELECTEDVALUE('Table A'[Skills Name])
var _b = HASONEVALUE('Table A'[Skills Name])
var _c = countrows( FILTER('Table B', 'Table B'[Skills Requested] = _a ))
Return 
if (_b, if (ISBLANK(_c), 0, _c), BLANK() )

     

    Output:

     

sevenhills's avatar
sevenhills
Icon for Super User rankSuper User
4 years ago
Measure = 
var _a = SELECTEDVALUE('Table A'[Skills Name])
var _b = HASONEVALUE('Table A'[Skills Name])
var _c = countrows( FILTER('Table B', 'Table B'[Skills Requested] = _a ))
Return 
if (_b, if (ISBLANK(_c), 0, _c), BLANK() )

 

Output:

 

  • byr10112's avatar
    byr10112
    Icon for Microsoft Employee rankMicrosoft Employee
    4 years ago

    Thanks for your reply. This measure returns the same number for every single entry. There are no blanks or 0s but there should be some. 

    • sevenhills's avatar
      sevenhills
      Icon for Super User rankSuper User
      4 years ago

      Not clear what you are saying ... what exactly you need?

       

      I took your input tables and created measue you want in output table and matched it. 

      • byr10112's avatar
        byr10112
        Icon for Microsoft Employee rankMicrosoft Employee
        4 years ago

        This measure returns 2663 for every single row in Table A, even if they do not exists in Table B. I don't know where 2663 is coming from. What you did makes sense to me but I don't know why it's not working in my report.  

  • meIdo's avatar
    meIdo
    Icon for Helper I rankHelper I
    3 years ago

    How do I create occurence column instead of measure?

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hey, thanks for this!
    Is there a way to do the same but as a column?