Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
i have a completion coloumn, that holds a date if a case is completed and is blank if the case is still open.
is there a method to count
if there is a completion date and
if it is greater than the max date of the the filter selection.
Solved! Go to Solution.
Hi @NewbieJono
Max(datetable[Date]) is a dynamic function to return the max date you select in your slicer or filter. To use this dynamic value in your measure, you can use VAR function to get the dynamic value and then use it.
I build a sample and update the measure, you can try this way.
My Sample:
Build an unrelated Date table for filter.
Date = CALENDAR(DATE(2021,01,01),DATE(2021,07,31))
Measure:
Completed after Max Chosen Date =
VAR _MaxFilterDate =
MAX ( 'Date'[Date] )
VAR _IsComplete =
CALCULATE ( COUNTROWS ( 'Sample' ), 'Sample'[Completion] <> BLANK () )+0
VAR _AfterFilterIsComplete =
CALCULATE ( COUNTROWS ( 'Sample' ), 'Sample'[Completion] >= _MaxFilterDate )
VAR _Result =
IF ( ISFILTERED ( 'Date'[Date] ), _AfterFilterIsComplete, _IsComplete )+0
RETURN
_Result
Result is as below.
By default it will show count of cases which are completed.
When I select 2021/01/19 and 2021/05/08 in Slicer it will count the cases which are completed after the max filter date (2021/05/08).
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hey @NewbieJono ,
yes, you can check that with measures.
For the check if it's completed you should try something like that:
Is completed =
CALCULATE(
COUNTROWS( myTable ),
myTable[completion column] <> ""
)
And for the check about the completion date the following measure should work:
Completed after Max Chosen Date =
CALCULATE(
COUNTROWS( myTable ),
myTable[completion column] >= MAX(myDateTable[Date])
)
i seem to be getting this error
Hi @NewbieJono
Max(datetable[Date]) is a dynamic function to return the max date you select in your slicer or filter. To use this dynamic value in your measure, you can use VAR function to get the dynamic value and then use it.
I build a sample and update the measure, you can try this way.
My Sample:
Build an unrelated Date table for filter.
Date = CALENDAR(DATE(2021,01,01),DATE(2021,07,31))
Measure:
Completed after Max Chosen Date =
VAR _MaxFilterDate =
MAX ( 'Date'[Date] )
VAR _IsComplete =
CALCULATE ( COUNTROWS ( 'Sample' ), 'Sample'[Completion] <> BLANK () )+0
VAR _AfterFilterIsComplete =
CALCULATE ( COUNTROWS ( 'Sample' ), 'Sample'[Completion] >= _MaxFilterDate )
VAR _Result =
IF ( ISFILTERED ( 'Date'[Date] ), _AfterFilterIsComplete, _IsComplete )+0
RETURN
_Result
Result is as below.
By default it will show count of cases which are completed.
When I select 2021/01/19 and 2021/05/08 in Slicer it will count the cases which are completed after the max filter date (2021/05/08).
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |