Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
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.
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
108 | |
98 | |
81 | |
65 | |
62 |
User | Count |
---|---|
147 | |
116 | |
104 | |
88 | |
65 |