Count number of active articles divided by country at the end of each week

[rpapier]

Hi,

I'm stuck on how to count number of active articles divided by country at the end of each week. I have 2 tables - key in the relation is Article ID.

1=active, 0=inactive in [Active] column.

Date	Weeknum	Active	Article ID
01-01-2022	52	1	100
01-01-2022	52	1	200
01-01-2022	52	1	300
01-01-2022	52	1	400
01-01-2022	52	1	500
02-01-2022	52	0	200
04-01-2022	1	0	100
08-01-2022	1	0	300
09-01-2022	1	1	200
09-01-2022	1	0	500
09-01-2022	1	1	100
12-01-2022	2	0	400
13-01-2022	2	0	200
14-01-2022	2	1	300
15-01-2022	2	0	100
15-01-2022	2	1	500
16-01-2022	2	1	200
17-01-2022	3	0	500
17-01-2022	3	0	300
23-01-2022	3	1	400

Article ID	Country
100	UK
200	USA
300	UK
400	USA
500	DE

Output expected

Count Articles Active	Country	Weeknum
2	UK	52
1	USA	52
1	DE	52
1	UK	1
2	USA	1
0	DE	1
1	UK	2
1	USA	2
1	DE	2
0	UK	3
2	USA	3
0	DE	3

[v-yiruan-msft]

Hi @rpapier ,

What's the calculation logic of getting [Count Articles Active]? Could you please explain it base on your provided data and specific example? I created a sample pbix file(see the attachment), but there is difference with your expected output. I'm not sure what do you mean of "count number of active articles divided by country"... 

Count Articles Active = 
VAR _acount =
    CALCULATE ( COUNT ( 'Table1'[Article ID] ), 'Table1'[Active] = 1 )
RETURN
    _acount + 0

Best Regards