Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
tsoulge
Helper I
Helper I

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

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):

tsoulge_0-1686039659526.png

District ID District Name   Start Date End Date
1District A01/01/2001/01/22
1District A01/01/22 
2District B01/01/2001/01/22
2District B New01/01/2201/01/23
2District B New01/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
    1District A01/01/22 
    2District B New01/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 IDDistrict Name Start Date End Date
    1District A01/01/22 
    2District B01/01/2001/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 IDDistrict Name Start Date End Date
    1District A01/01/2001/01/22
    2District B01/01/22 
     Many thanks for your help.
1 ACCEPTED SOLUTION
tamerj1
Super User
Super User

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 ) )

View solution in original post

4 REPLIES 4
tamerj1
Super User
Super User

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 ) )

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!

@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 ) )

 

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!

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors