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.
Hi,
I have a table named Table_fault_test as following.
FaultReportedTime | FaultCorrectedTime | FaultID |
2019-01-02 | 2019-01-20 | a |
2019-01-23 | 2019-02-12 | b |
2019-01-30 | 2019-02-12 | c |
2019-02-12 | 2019-02-23 | d |
2019-02-18 | 2019-03-01 | e |
2019-02-25 | 2019-02-28 | f |
2019-03-01 | 2019-03-05 | g |
2019-03-24 | 2019-03-30 | h |
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]) ) )
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!
Solved! Go to Solution.
Hi @Anonymous ,
To create a measure as below.
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 ) )
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.
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...
Hi @Anonymous ,
To create a measure as below.
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 ) )
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 ) )
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.
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.
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
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 |
---|---|
110 | |
94 | |
81 | |
66 | |
58 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |