Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
v-frfei-msft
Community Support
Community Support

Hi @Anonymous ,

 

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 others find it more quickly.

View solution in original post

9 REPLIES 9
Ashish_Mathur
Super User
Super User

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/
Anonymous
Not applicable

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

 

v-frfei-msft
Community Support
Community Support

Hi @Anonymous ,

 

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 others find it more quickly.
Anonymous
Not applicable

@v-frfei-msft 

 

thanks a lot, it do work.

 

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

 

Regards

Hi @Anonymous ,

 

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 others find it more quickly.
Anonymous
Not applicable

@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

Hi @Anonymous ,

 

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 others find it more quickly.
Anonymous
Not applicable

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

Anonymous
Not applicable

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.