topic Re: Filter rows where SELECTEDYEAR() is in between two columns in DAX Commands and Tips

Filter rows where SELECTEDYEAR() is in between two columns

Anonymous — Mon, 04 Mar 2024 15:40:07 GMT

Help!

I have a hard time solving this, from a programming perspective, easy task in DAX.

What I want to solve:

1. User selects a year from a slicer. This year comes from a date table

2. All rows where the selected years are in between FromDate and ToDate should be returned.

3. From here, I want to DISTINCTCOUNT() an ID and display them in different visuals, using different categories, accross the page.

What I've tried so far:

M = CALCULATE( DISTINCTCOUNT('datastore X'[ID]), FILTER ( ALL('datastore X'[FromYear], 'datastore X'[ToYear], 'datastore X'[ID]), 'datastore X'[FromYear] <= [SelectedYear] && 'datastore X'[ToYear] >= [SelectedYear] ) )

Where [SelectedYear] = SELECTEDVALUE('datastore Y'[Year]).

The problem with this measure is that if the user selects e.g. 2023, this filter on the table will first be applied before the measure is executed.

Any help is appreciated!

Re: Filter rows where SELECTEDYEAR() is in between two columns

timalbers — Mon, 04 Mar 2024 15:22:14 GMT

Hi Anonymous ,
in order to help you with this, I will need to have some more information about how your model is set up.
Especially on how the date table is linked to your fact table and also what type of data exactly is in the fromDate and toDate columns. Years or dates?

Re: Filter rows where SELECTEDYEAR() is in between two columns

Anonymous — Mon, 04 Mar 2024 15:53:32 GMT

Hi,

Thanks for the fast reply

In fromDate and toDate, as well as date column in date table are of typ date

The relationship between fromDate and date is active

The relationship between toDate and date is inactive

These are the once I am using in my relationship.

What I forgot to provide is details on how I extract the YEAR.

fromDate and toDate have two respective calculated columns, called fromYear and toYear. These are the ones I am using in my measure.

I will make sure to edit that in the original post.

Re: Filter rows where SELECTEDYEAR() is in between two columns

timalbers — Mon, 04 Mar 2024 16:12:36 GMT

Ok, if you are only comparing years, and not whole dates, then something like this probably would already work:

M = VAR min_year = MIN( date_table[Year] ) VAR max_year = MAX( date_table[Year] ) RETURN CALCULATE( DISTINCTCOUNT( 'datastore X'[ID] ), 'datastore X'[FromYear] <= min_date && 'datastore X'[ToYear] >= max_date )

Re: Filter rows where SELECTEDYEAR() is in between two columns

Anonymous — Mon, 04 Mar 2024 16:32:43 GMT

I appreciate your help and I think this is a good start, however records that have
FromYear < Selected year > ToYear

So, e.g., if selected year is 2023, records that have
FromYear: 2018
ToYear: 2019

are still included in the resulting table visualisation.

Re: Filter rows where SELECTEDYEAR() is in between two columns

Anonymous — Mon, 11 Mar 2024 07:08:41 GMT

Hi Anonymous ,

You can try to modify your formula with reference to the following:

M_ = CALCULATE( DISTINCTCOUNT('datastore X'[ID]), FILTER( 'datastore X', 'datastore X'[fromYear] <= [SelectedYear] && 'datastore X'[toYear] >= [SelectedYear] ) )

Best Regards,
Adamk Kong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Filter rows where SELECTEDYEAR() is in between two columns

Anonymous — Mon, 11 Mar 2024 09:37:00 GMT

Hi Adam,

THank you for your response, I appreciate it.

Your solution is almost correct, however in your example with the provided measure M_, the person with ID 3 will not show up in the table if the selected year is 2024. In my case, I need that to happen as the ID 3 person was active 2024 given its to and from date (2023 and 2025).

Do you have any suggestion on how to fix that?

Best,

Aksel