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 have the following table:
Nº | START DATE | END DATE |
1 | 01/04/2010 | 08/06/2014 |
2 | 08/06/2010 | 10/04/2015 |
3 | 06/06/2012 | 08/04/2015 |
4 | 01/10/2012 | 06/08/2016 |
5 | 04/03/2013 | 10/10/2016 |
6 | 06/06/2013 | 14/11/2017 |
7 | 14/04/2014 | 15/12/2017 |
8 | 20/11/2014 | 10/06/2017 |
9 | 15/11/2015 | |
10 | 16/10/2016 | |
11 | 14/02/2017 | |
12 | 08/08/2017 | |
13 | 20/02/2018 | |
14 | 04/05/2018 |
This table is related to a calendar table through the start date.
I have a slicer created with the year of the calendar table. What I want is to know how I can count the data in the end date column in each year.
For example, if you filter in 2010, the number to count of the end date column is 0 and if you filter in 2015, the number to count of the end date column is 2.
Thank you very much and best regards.
Solved! Go to Solution.
Hello @Anonymous and @Greg_Deckler,
I found the solution with the USERELATIONSHIP function. As you said, the end date column is related to the calendar table, with the inactive relation. Then I created the following formula:
MEASURE = CALCULATE (COUNT(TABLE1(END_DATE)); USERELATIONSHIP(TABLE1(END_DATE);CALENDARTABLE(DATE))
The USERELATIONSHIP function activates the relation for the calculation.
Thank you very much and best regards.
Measure = COUNTROWS(VALUES('Table'[END DATE]))
?
Hello @Greg_Deckler,
I have tried the measurement but the result that it gives me is erroneous.
Thank you very much for your help and best regards.
Hi @Isidro,
Have you tried: Measure = COUNTA(Table1[END DATE]) ?
This gives me the answers you are looking for. Hope this helps.
Edit: note that I have related the Date table with the END DATE
Hello @Anonymous and @Greg_Deckler,
I found the solution with the USERELATIONSHIP function. As you said, the end date column is related to the calendar table, with the inactive relation. Then I created the following formula:
MEASURE = CALCULATE (COUNT(TABLE1(END_DATE)); USERELATIONSHIP(TABLE1(END_DATE);CALENDARTABLE(DATE))
The USERELATIONSHIP function activates the relation for the calculation.
Thank you very much and best regards.
Hello @Anonymous,
It works if I change the relationship of the calendar table with the column, but I also need to do a count with the start date column. I need to be able to count with the existing relationship.
Thank you very much for your help and best regards.
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 |
---|---|
113 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |