Trouble ignoring filter in calculated measure

[Anonymous]

So I have a table like this:

ALL_SN

Serial Number | Region

Linked by Serial Number to this one:

CONTRACT

Serial Number | Contract | Start Date | End Date

Where I can have multiple Serial Numbers in one contract, and also have multiple contracts for a single Serial Number during time - never two contracts at the same time for the same Serial Number, so you filter out the active contract using the End Date.

I also have a calendar table linked to the one above by Start Date.

In a report page I have a graph with the YTD revenue and a slicer to choose which year I want to analyze. I have a second graph with the same YTD revenue per Region and the quantity of Serial Numbers in each region (stacked/line chart).

The issue is: the contracts usually last for 5 years or more. So when I filter year using the slicer, the serial number quantity gets choped to the quantity that had their start date in that year. For example: I know for a fact that we had contracts for 685 Serial Numbers in 2020, but when I filter the year 2020, the number showing is 149 - which are the contracts that started in 2020. I wanted to have all the Serial Numbers quantity despite the start date.

The measure below gives me the accurate Serial Number quantity active today.

Active Contract = 
CALCULATE(
    DISTINCTCOUNT(
        Contract[Serial Number]
    ),
    FILTER(
        ALL(Contract),
        Contract[End Date] >= TODAY()
    )
)

If I use this measure as Values and the Region as Axis in the graph, I get the accurate information IF my Year slicer filter is clear. I wanted to keep the same information regardless of the filter.

In the image below you can see that Region 6 even disappears as year 2020 is selected.

[Anonymous]

Hi @Anonymous 

6 in visual disappear may be caused that there isn't 6 in 2020.

I think it is a good way to build an unrelated Date table to build your year slicer.

Try to build a calcualted table by Calendar/Calendarauto function.

Date = Addcolumns(calendarauto(),'Year',Year([Date]))
OR
Date = Addcolumns(calendar(Min(Contract[Start Date]),Max(Contract(Start Date))),'Year',Year([Date]))

Then update your measure as below.

Active Contract = 
VAR _Sel = Selectedvalue(Date[Year])
Return
CALCULATE(
    DISTINCTCOUNT(
        Contract[Serial Number]
    ),
    FILTER(
        ALL(Contract),
        Contract[Year]=_Sel&&
        Contract[End Date] >= TODAY()
    )
)

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. 

[Anonymous · Accepted solution]

Ok, I think you didn't understand the issue. I dont't want  to filter the active serial number per region by year, only by region.

I ended up figuring it out, very simple. Instead of using ALL in the filter, which returned all regions with the total amount of serial numbers, I used ALLEXCEPT, so it's ignoring all filters except the Region.

Active Serial Number per Region = 
CALCULATE(
    DISTINCTCOUNT(
        ALL_SN[Serial Number] //instead of Contract[Serial Number]
    ),
    FILTER(
        ALLEXCEPT( Contract, ALL_SN[Region] ),
        Contract[expiration_date] >= DATE( YEAR( TODAY() ), MONTH ( TODAY() ), 1 )
    )
)

Thank you for the help though!

[lbendlin]

Read up about the concept of disconnected tables. Use a disconnected table to feed your date selection slicer, then use measures to query the selected value of the slicer.