Matrix is not properly filtering with measure

[Anonymous]

Hi! There is this problem. I created a sample of data and a simple measure.

And now when I filter this matrix, all dates are still there:

How can I change the measure with this logic (I want to see 0 if spend is empty) so that it will be filtering as with regular sum:

[v-luwang-msft · Accepted solution]

Hi @Anonymous ，

Test like below:

1.create a date table:

Date = CALENDAR("2021,1,1","2021,12,31")

2.create relationship:

3.create the below measure:

qq = 
VAR maxdate =
    CALCULATE ( MAX ( 'Date'[Date] ), ALLSELECTED ( 'Date' ) )
VAR mindate =
    CALCULATE ( MIN ( 'Date'[Date] ), ALLSELECTED ( 'Date' ) )
RETURN
    IF (
        MAX ( 'Table'[date] ) < mindate
            || MAX ( 'Table'[date] ) > maxdate,
        BLANK (),
        IF (
            MAX ( 'Table'[date] ) >= mindate
                && MAX ( 'Table'[date] ) <= maxdate,
            MAX ( 'Table'[spent] ) + 0,
            BLANK ()
        )
    )

Final get：

You could download my pbix file if you need!

Did I answer your question? Mark my post as a solution!

Best Regards

Lucien

[amitchandak]

@Anonymous , Based on what I got. You want 0 only when it blank between range. Try a measure like

0 between range
Measure = var _1= SUM(Opportunity[Opportunity count]) +0
var _min = minx(ALLSELECTED('Calendar'), 'Calendar'[Date])
var _max = maxx(ALLSELECTED('Calendar'), 'Calendar'[Date])
return
CALCULATE(if(max('Calendar'[Date]) <_min || max('Calendar'[Date]) >_max , BLANK(), _1))