powerbidaxdata-analysispowerbi-desktopmeasure

Need help in filtering using measure in power bi

I have 2 measures in my power bi report which show cumulative trend based on two date columns namely created date and closed date. The 2 trend lines show total number of bugs created vs total number of closed bugs at specific dates. enter image description here

Total Created Bugs:

Measure1 = CALCULATE(COUNTROWS(Sheet1),FILTER(ALLSELECTED(Sheet1),Sheet1[CreatedDate]<=MAX(Sheet1[CreatedDate])))

Total Closed Bugs:

Measure2 = var totalEpics= CALCULATE( COUNTROWS(Sheet1), FILTER ( ALL(Sheet1), IF(Sheet1[ClosedDate] <> BLANK() ,Sheet1[ClosedDate] <= MAX(Sheet1[CreatedDate]),0))) var val = IF(ISBLANK(totalEpics),0,totalEpics) RETURN val

enter image description here

Issue is that If I change the date in slicer then measure1 is recalculated correctly but measure 2 is not recalculating according to slicer

enter image description here

Solution

  • What happens if you replace ALL with ALLSELECTED

       Measure2 = 
VAR totalEpics = 
    CALCULATE(
        COUNTROWS(Sheet1), 
        FILTER(
            ALLSELECTED(Sheet1), 
            IF(Sheet1[ClosedDate] <> BLANK(), Sheet1[ClosedDate] <= MAX(Sheet1[CreatedDate]), 0)
        )
    )
VAR val = IF(ISBLANK(totalEpics), 0, totalEpics) 
RETURN val