Measure to filter distinct products by year

[tonyclifton]

Hello community,

in below example picture I want to be able to filter distinct Products by Year.

I have created a Calendar table that is joined to both Budget and Forecast table.

So setting a year range from 2019 to 2023 would return only one product.

This formula would probably work if I had a date in my Product table:

CALCULATE (
    DISTINCTCOUNT ( Budget[ProductID] );
    ALLEXCEPT ('Calendar';'Calendar'[Year])
)

The goal is to create a tooltip in a chart which only shows products for a selected time range.

I hope you have an idea how to solve this.

Thank you.

[v-chuncz-msft · Accepted solution]

@tonyclifton,

You may use Append Queries (Table.Combine) in Query Editor or UNION Function (DAX) to create a new table.