Forum Discussion

teedee29's avatar
teedee29
Frequent Visitor
5 years ago
Solved

How to perform "left join" filter?

I have the following measure in Table A:
Task Count = COUNTROWS(FILTER('Table B',CONTAINS('Table A',[Work Item Id],'Table B'[Parent Work Item Id])))
 
There's nothing wrong with the measure, but when I add the "task Count" measure to a grid that contains ALL fields from Table A thus far, the data will be filtered to only return data that has task count > 0. If there anyway I can do this where it will return Task Count of 0 if there are no matching records in Table B?
Tutorial Requests
  • teedee29's avatar
    teedee29
    5 years ago

    The first option +0 did not work since it did some kind of cartesian and listed all the data from Table B. The second option ideally would work but I coudln't find the "show items with no data" when i click on the column in the grid and it didn't have that option.

     

     

    I ended up trying this:

    add new column = 

    CALCULATE(COUNT('Table B'[Work Item Id]), FILTER('Table B', [Parent Work Item Id] = 'Table A'[Work Item Id]))
     
    Then i set the new column in the grid display as "don't summarize"

2 Replies

  • AllisonKennedy's avatar
    AllisonKennedy
    Icon for Community Champion rankCommunity Champion
    5 years ago

    teedee29 If I understand your question correctly I think you have a couple options:

     

    Option A: Add zero to your measure: 

    Task Count = COUNTROWS(FILTER('Table B',CONTAINS('Table A',[Work Item Id],'Table B'[Parent Work Item Id]))) + 0

     

    Option B: To show blank, (not zero), you can use the "Show Items with no data" option. 

     

    If that doesn't work, let me know what result that gives and why it's wrong, and how the tables A and B are related

    • teedee29's avatar
      teedee29
      Frequent Visitor
      5 years ago

      The first option +0 did not work since it did some kind of cartesian and listed all the data from Table B. The second option ideally would work but I coudln't find the "show items with no data" when i click on the column in the grid and it didn't have that option.

       

       

      I ended up trying this:

      add new column = 

      CALCULATE(COUNT('Table B'[Work Item Id]), FILTER('Table B', [Parent Work Item Id] = 'Table A'[Work Item Id]))
       
      Then i set the new column in the grid display as "don't summarize"