How to count number of months with data

[Rakel]

Hello,

I need some help to write a DAX formula to be able to count how many rows there are for each pair (customer, year) without taking into account the context filters. 

An example of data are these (table1):

id_customer  Year  month

1	2022	9
1	2022	10
1	2022	11
1	2022	12
1	2023	1
1	2023	2
2	2023	2
3	2023	1
3	2023	2
3	2023	3

So, for this example I need a measure to count how many records there are for customer 1 in the year 2022 (4), how many for customer 1, year 2023(2) , how many for customer 2, year 2023 (1), and how many for customer 3, year 2023 (3). 

The difficult point for me is that there are some filter which are filtering the month, so my measure is always retrieving 1. I have tried to remove this using a calculate formula but it is not working.

number of months with data = calculate(countrows(table1),allexcept(table1,table1[year],table1[id_customer])) 

Can someone explain me why is not working and how I can fix it? 

Thanks in advance,

 R.

[Adamboer]

The DAX formula you provided is almost correct. However, instead of using "ALLEXCEPT", you should use "VALUES" to preserve the filters on the "Month" column. Here is the corrected formula:

number of months with data = CALCULATE(COUNTROWS(table1), VALUES(table1[id_customer]), VALUES(table1[Year]))

This formula will count the number of rows in "table1" for each combination of "id_customer" and "Year", without taking into account any filters applied to the "Month" column. The "VALUES" function is used to preserve the filters on the other columns while removing any filters on the "Month" column.

[Rakel]

Hello @Adamboer 

thank you very much for your answer. I am afraid I didn't explain it correctly as the formula is taking into account the month.

If I filter the year and the id_customer, it is working properly. But, if I add the month, it is also taken into account. That's exactly what I want to avoid.  I have added a card to show the value of the measure. Look at below examples:

 Best regards,

  Rakel

[Arul]

@Rakel ,

In which visual do you need to show?

Thanks,

Arul

[Rakel]

Hello @Arul,

Thanks for answering my question. This measure will be part of a formula I need. For "debugging" I am now adding it on a card.

Thanks,

  Rakel