cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Isidro
Helper IV
Helper IV

Measure that counts on a date column other than the date column that is used in a slicer.

Hello,

 

I have the following table:

 

START DATEEND DATE
101/04/201008/06/2014
208/06/201010/04/2015
306/06/201208/04/2015
401/10/201206/08/2016
504/03/201310/10/2016
606/06/201314/11/2017
714/04/201415/12/2017
820/11/201410/06/2017
915/11/2015 
1016/10/2016 
1114/02/2017 
1208/08/2017 
1320/02/2018 
1404/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.

1 ACCEPTED SOLUTION

Hello @Koen179 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.

View solution in original post

5 REPLIES 5
Greg_Deckler
Super User IV
Super User IV

 

Measure = COUNTROWS(VALUES('Table'[END DATE]))

 

?


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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.

Koen179
Frequent Visitor

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 @Koen179 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.

View solution in original post

Hello @Koen179,

 

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.

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors