cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
hongjyan Regular Visitor
Regular Visitor

Count the number of date column in two date column table

Hi,

I have a table named Table_fault_test as following.

 

FaultReportedTime

FaultCorrectedTimeFaultID

2019-01-02

2019-01-20a
2019-01-232019-02-12b
2019-01-302019-02-12c
2019-02-122019-02-23d
2019-02-182019-03-01e
2019-02-252019-02-28f
2019-03-012019-03-05g
2019-03-242019-03-30h

 

And a normal Calender Date table whose date column has one-to-one plus both relationship with the FaultReportedTime colum of Table_fault_test.

I want to use calender date as my axis still and get monthly corrected fault number , which should be

2019-01    1

2019-02    4

2019-03    3

 

I tried formula

Count Of monthly closed Fault = 
CALCULATE(DISTINCTCOUNT(Table_fault_test[FaultID]),
          FILTER(ALL(Table_fault_test), 
                 Table_fault_test[FaultCorrectedTime] >= STARTOFMONTH(CalenderDate[Date])
                 && Table_fault_test[FaultCorrectedTime] <= ENDOFMONTH(CalenderDate[Date])
          )
)

But it show all result for each month which is wrong, I guess the reason is the expression of filter still be overrided by ALL .

And of course the result of formula without ALL below

Count Of monthly closed Fault = 
CALCULATE(DISTINCTCOUNT(Table_fault_test[FaultID]),
          FILTER(Table_fault_test, 
                 Table_fault_test[FaultCorrectedTime] >= STARTOFMONTH(CalenderDate[Date])
                 && Table_fault_test[FaultCorrectedTime] <= ENDOFMONTH(CalenderDate[Date])
          )
)

CountOfMonthlyCorrectedFault.PNG

is not correct since it only show the number which is reported and closed at the same month.

 

I am lost now, any help is appreciate!

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Count the number of date column in two date column table

Hi @hongjyan ,

 

To create a measure as below.

Capture.PNG

 

Count Of monthly closed Fault = var mind =  MIN('CalenderDate'[Date])
var maxd = MAX('CalenderDate'[Date])
return
CALCULATE(DISTINCTCOUNT(Table_fault_test[FaultID]),
          FILTER(ALL(Table_fault_test), 
                 Table_fault_test[FaultCorrectedTime] >= mind
                 && Table_fault_test[FaultCorrectedTime] <= maxd
          )
)
Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

9 REPLIES 9
hongjyan Regular Visitor
Regular Visitor

Re: Count the number of date column in two date column table

Solved it by https://community.powerbi.com/t5/Desktop/Count-rows-by-month-for-2-different-date-columns/td-p/49589....

 

For learnning process, is there anyone can tell me how to made it without use "userelationship"? what's wrong with ALL?

 

Regards

Community Support Team
Community Support Team

Re: Count the number of date column in two date column table

Hi @hongjyan ,

 

To create a measure as below.

Capture.PNG

 

Count Of monthly closed Fault = var mind =  MIN('CalenderDate'[Date])
var maxd = MAX('CalenderDate'[Date])
return
CALCULATE(DISTINCTCOUNT(Table_fault_test[FaultID]),
          FILTER(ALL(Table_fault_test), 
                 Table_fault_test[FaultCorrectedTime] >= mind
                 && Table_fault_test[FaultCorrectedTime] <= maxd
          )
)
Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

hongjyan Regular Visitor
Regular Visitor

Re: Count the number of date column in two date column table

@v-frfei-msft 

 

thanks a lot, it do work.

 

could you point why min/max works rather than startofmonth/endofmonth?

 

Regards

Community Support Team
Community Support Team

Re: Count the number of date column in two date column table

Hi @hongjyan ,

 

This formula should work well as well. The key is to define the start date and end date in advance.

 

Count Of monthly closed Fault 2 = var mind =  STARTOFMONTH(CalenderDate[Date])
var maxd = ENDOFMONTH('CalenderDate'[Date])
return
CALCULATE(DISTINCTCOUNT(Table_fault_test[FaultID]),
          FILTER(ALL(Table_fault_test), 
                 Table_fault_test[FaultCorrectedTime] >= mind
                 && Table_fault_test[FaultCorrectedTime] <= maxd
          )
)
Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
hongjyan Regular Visitor
Regular Visitor

Re: Count the number of date column in two date column table

@v-frfei-msft 

what's the difference between defining start/end date inside and outside? I surmise it should be related to some context thing, is ALL made start/end date's context expanding to whole table? would you demystify it?

I met the difference before mentioned at https://community.powerbi.com/t5/Desktop/should-not-use-function-as-part-of-calculate-filter-boolean..., do not know if it is caused by the same or similar reason.

 

Regards

Community Support Team
Community Support Team

Re: Count the number of date column in two date column table

Hi @hongjyan ,

 

Yes, the ALL function makes the filter cannot get the excepted reuslt in your scenario.

 

 

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Super User
Super User

Re: Count the number of date column in two date column table

Hi,

The way you are describing it, there is no relevance of column 1.  Therefore, create an active relationship from column 2 to the Date column of the Calendar Table.  To your visual, drag Year and Month from the Calendar Table.  Write this measure

Measure=COUNTROWS(Data)

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
hongjyan Regular Visitor
Regular Visitor

Re: Count the number of date column in two date column table

@Ashish_Mathur

 

thanks, Apologize that i did not mention the key cloumn 1 is the prime relationship which used by other visuals, so I can not make column 2 active. 

but,  still it can solved by relationship with function "userelationship" as describe in https://community.powerbi.com/t5/Desktop/Count-rows-by-month-for-2-different-date-columns/td-p/49589...

 

hongjyan Regular Visitor
Regular Visitor

Re: Count the number of date column in two date column table

@v-frfei-msft 

When I read section "FILTER is an iterator that loops through all the rows of a table and returns a new table containing only the ones that satisfy the condition defi ned by the second parameter." from the "The Definitive Guide to DAX" , I realised it is FILTER changing the behavior to STARTOFMONTH/ENDOFMONTH when they are nested since nested STARTOFMONTH/ENDOFMONTH are in row context which is imported by FILTER.

But I also try MIN/MAX, but seems MIN/MAX 's behavior does not change no matter it is nested in FILTER or not. I do not know why.

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Users Online
Currently online: 241 members 2,779 guests
Please welcome our newest community members: