Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Count rows from another table

Hi,

 

I have two tables: AllGroups - with the list of Group titles (62 rows) and SummaryAboutGroups - with execution dates of those groups (1799 rows):

I need to get this answer: how many groups were executed in special period of time - this week/last week/today and so on.

So the result should be one table of 62 rows with two columns:

 

Group|Number of groups

 

If in that period of time some Groups were not executed, there should be zeros in Number of Groups column.

Need Help
  • v-zhenbw-msft's avatar
    v-zhenbw-msft
    6 years ago

    Hi Anonymous ,

     

    We can use the following steps to meet your requirement.

     

    1. In Power Query Editor, we need to add a date column and a week column.

     

     

     

    2. Create three measures.

     

    Today number of Group = 
var _today = TODAY()
return
CALCULATE(COUNT('Table'[Group]),FILTER('Table','Table'[Date]=_today))

     

    This week number of Group = 
var _thisweek = WEEKNUM(TODAY(),2)
return
CALCULATE(COUNT('Table'[Group]),FILTER('Table','Table'[Week of Year]=_thisweek))

     

    Last week number of Group = 
var _lastweek = WEEKNUM(TODAY(),2)-1
return
CALCULATE(COUNT('Table'[Group]),FILTER('Table','Table'[Week of Year]=_lastweek))

     

     

    If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?

     

    Best regards,

     

    Community Support Team _ zhenbw

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

    BTW, pbix as attached.

4 Replies