DAX formula to create a table with unique values per ID based on period selection

[tsoulge]

Hi all,

I have created a dashboard with a date slicer using a "between" style and I want to add a table showing unique Sales District names. In each District we assign a Sales rep, so each one has a Start date and an End Date, based on Sales Reps assignments (if we have a no End Date value, means that the District is active until today):

District ID	District Name	Start Date	End Date
1	District A	01/01/20	01/01/22
1	District A	01/01/22
2	District B	01/01/20	01/01/22
2	District B New	01/01/22	01/01/23
2	District B New	01/01/23

I want to create a DAX expression/flag based on the following rules/scenarios, in order to show only unique/one row per District ID, in any period selection from the user:

1. When the user selects a period that many Districts with the same ID are available, as we have in the table above, only the latest one should be visible in the table. So, if the user selects all dates in the slicer, the table should be like this one: 

   District ID	District Name	Start Date	End Date
   1	District A	01/01/22
   2	District B New	01/01/23

2. If the user selects a period in the date slicer that doesn't include any of Districts per ID, the table should be empty (for example if he selects dates only in 2019).
3. If the user selects the period from 01/01/2019 until 31/12/21, only the 1st row of each District ID should be visible (since in this period range only the 1st assignment is included).

   District ID	District Name	Start Date	End Date
   1	District A	01/01/22
   2	District B	01/01/20	01/01/22

4. If the user extends his period selection, from 01/01/2019 to 31/12/22, the second row of each District ID should be visible (since in this period range we have 2 District assignments, but we should always show the latest as mentioned before).

   District ID	District Name	Start Date	End Date
   1	District A	01/01/20	01/01/22
   2	District B	01/01/22

    Many thanks for your help.

[tamerj1 · Accepted solution]

Hi @tsoulge 
Please the following measure in the filter name of the table visual, select "is not blank" then apply the filter.

FilterMeasure =
VAR MaxStart =
    MAXX (
        CALCULATETABLE (
            VALUES ( 'Table'[Start Date] ),
            ALLSELECTED (),
            VALUES ( 'Table'[District ID] )
        ),
        'Table'[Start Date]
    )
RETURN
    COUNTROWS ( FILTER ( 'Table', 'Table'[Start Date] = MaxStart ) )

[tsoulge]

Hi @tamerj1 ,

Many thanks for your quick response. I created this measure and I used it in the filter name, as you described, but the result is not the expected one, since in any selection I made from the date slicer, the table always shows the latest assignment. The result should respect the rules I described in my initial message based on the selections of the user in the Date slicer (such as if the user selects a date range before the Start date of any District, the table should show 0 results).

Important update: I forgot to mention that I use 2 different tables:

• 'Date', where I use the [Date] column in my slicer
• 'District', where I have the [District ID], the [District Name], the [Start Date] and the [End Date] of assignments.

There is a relationship with a single link from 'Date'[Date] to 'District'[Start Date].  

Thanks again for your help!

[tamerj1]

@tsoulge 
This is what ALLSELECTED ( ) does. Strange it didn't work. Please try it this way

FilterMeasure =
VAR MaxStart =
    MAXX (
        CALCULATETABLE (
            VALUES ( 'Table'[Start Date] ),
            ALL ( 'Table'[Start Date], 'Table'[End Date] )
        ),
        'Table'[Start Date]
    )
RETURN
    COUNTROWS ( FILTER ( 'Table', 'Table'[Start Date] = MaxStart ) )

[tsoulge]

Hi @tamerj1 ,

The initial measure works fine! There was a mistake in the data model that I fixed and your DAX command does exactly what I wanted.

Thanks again!