Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I am new to DAX formulas. I have spent ample amount of time to achieve my desired result, but I am not able to find solution.
I have TestDetails table and Date table. Below are details of contents in these 2 tables:
TestId | ReleaseDate | Department | ||||
Test1 | 11/1/2018 | A | Date | StartDate | EndDate | |
Test2 | 11/2/2018 | A | 11/1/2018 | 11/1/2018 | 11/7/2018 | |
Test3 | 11/3/2018 | A | 11/2/2018 | 11/2/2018 | 11/8/2018 | |
Test4 | 11/1/2018 | B | 11/3/2018 | 11/3/2018 | 11/9/2018 | |
Test5 | 11/2/2018 | A | 11/4/2018 | 11/4/2018 | 11/10/2018 | |
Test6 | 11/3/2018 | B | 11/5/2018 | 11/5/2018 | 11/11/2018 | |
Test7 | 11/4/2018 | C | 11/6/2018 | 11/6/2018 | 11/12/2018 | |
Test8 | 11/5/2018 | C | 11/7/2018 | 11/7/2018 | 11/13/2018 | |
Test9 | 11/6/2018 | D | 11/8/2018 | 11/8/2018 | 11/14/2018 | |
Test10 | 11/7/2018 | A | 11/9/2018 | 11/9/2018 | 11/15/2018 | |
Test11 | 11/8/2018 | A | 11/10/2018 | 11/10/2018 | 11/16/2018 | |
Test12 | 11/9/2018 | A | 11/11/2018 | 11/11/2018 | 11/17/2018 | |
Test13 | 11/1/2018 | B | 11/12/2018 | 11/12/2018 | 11/18/2018 | |
Test14 | 11/2/2018 | B | 11/13/2018 | 11/13/2018 | 11/19/2018 | |
Test15 | 11/3/2018 | C | 11/14/2018 | 11/14/2018 | 11/20/2018 | |
Test16 | 11/4/2018 | A | 11/15/2018 | 11/15/2018 | 11/21/2018 | |
Test17 | 11/5/2018 | A | 11/16/2018 | 11/16/2018 | 11/22/2018 | |
Test18 | 11/6/2018 | A | 11/17/2018 | 11/17/2018 | 11/23/2018 | |
Test19 | 11/7/2018 | A | 11/18/2018 | 11/18/2018 | 11/24/2018 | |
Test20 | 11/8/2018 | A | 11/19/2018 | 11/19/2018 | 11/25/2018 | |
Test21 | 11/1/2018 | B | 11/20/2018 | 11/20/2018 | 11/26/2018 | |
Test22 | 11/20/2018 | C | ||||
Test23 | 11/15/2018 | B | ||||
Test24 | 11/2/2018 | A |
I am trying to calculate no of test cases based on ReleaseDate between StartDate and EndDate. User can select date any date range using slicer on PowerBI dashboard. If user selects Department also, then no of test cases should be calculated based on department selected and between StartDate and EndDate.
The sample result I am expecting is as below:
Case 1 | Selected date from date slicer is 11/1/2018 and all department |
|
| Date | TestReleaseCount |
| 11/1/2018 | 19 |
Average |
| 19 |
Case 2 | Selected date from date slicer is 11/1/2018 and "A" department from Department Slicer |
|
| Date | TestReleaseCount |
| 11/1/2018 | 10 |
Average |
| 10 |
Case 3 | Selected date range from date slicer is 11/1/2018 to 11/4/2018 and all department |
|
| Date | TestReleaseCount |
| 11/1/2018 | 19 |
| 11/2/2018 | 17 |
| 11/3/2018 | 14 |
| 11/4/2018 | 11 |
Average |
| 15.25 |
case 4 | Selected date range from date slicer is 11/1/2018 to 11/4/2018 and "A" department from date slicer |
|
| Date | TestReleaseCount |
| 11/1/2018 | 10 |
| 11/2/2018 | 11 |
| 11/3/2018 | 9 |
| 11/4/2018 | 8 |
Average |
| 9.5 |
I came up with below formula, but it is not giving me desired result:
VAR abc = SELECTEDVALUE(TestDetails[Department], "ALL")
CALCULATE(COUNT(TestDetails[TESTID]),
FILTER(ALL(TestDetails), (TestDetails[Department] = abc)
&& (TestDetails[ReleaseDate]>=Min(Date[StartDate]))
&& (TestDetails[ReleaseDate]<=Max(Date[EndDate]))
)
)
Please help.
Solved! Go to Solution.
Give this a shot for the count:
TestReleaseCount = VAR MinDate = MIN(DateTable[Date]) VAR MaxDate = MAX(DateTable[Date]) VAR DateList = 1 RETURN CALCULATE( COUNT(TestDetails[TestId]), FILTER( ALL(DateTable[Date]), DateTable[Date] <= LOOKUPVALUE(DateTable[EndDate], DateTable[Date], MaxDate) && DateTable[Date] >= LOOKUPVALUE(DateTable[StartDate], DateTable[Date], MinDate) ) )
If you want the average, then you can do that with another measure that references the above:
Avg = AVERAGEX(VALUES(DateTable[Date]), [TestReleaseCount])
Hi @prl
For Case1 and Case2, you could create measures as below
min_selected = MIN('Date table'[StartDate]) max_selected = MAX('Date table'[EndDate]) Measure = CALCULATE ( COUNT ( TestDetails[TestId] ), FILTER ( ALLSELECTED ( TestDetails ), [ReleaseDate] >= [min_selected] && [ReleaseDate] <= [max_selected] ) )
For Case3 and Case4, it is a bit complex, i'm working on this and will update as soon as possible.
Best Regards
Maggie
For case 1 and 2 the provided solution works fine. Thank you!
Looking forward to solution for case 3 and case 4.
Give this a shot for the count:
TestReleaseCount = VAR MinDate = MIN(DateTable[Date]) VAR MaxDate = MAX(DateTable[Date]) VAR DateList = 1 RETURN CALCULATE( COUNT(TestDetails[TestId]), FILTER( ALL(DateTable[Date]), DateTable[Date] <= LOOKUPVALUE(DateTable[EndDate], DateTable[Date], MaxDate) && DateTable[Date] >= LOOKUPVALUE(DateTable[StartDate], DateTable[Date], MinDate) ) )
If you want the average, then you can do that with another measure that references the above:
Avg = AVERAGEX(VALUES(DateTable[Date]), [TestReleaseCount])
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
120 | |
101 | |
71 | |
61 |