Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
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:
District ID | District Name | Start Date | End Date |
1 | District A | 01/01/22 | |
2 | District B New | 01/01/23 |
District ID | District Name | Start Date | End Date |
1 | District A | 01/01/22 | |
2 | District B | 01/01/20 | 01/01/22 |
District ID | District Name | Start Date | End Date |
1 | District A | 01/01/20 | 01/01/22 |
2 | District B | 01/01/22 |
Solved! Go to Solution.
Hi @Anonymous
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 @Anonymous
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:
There is a relationship with a single link from 'Date'[Date] to 'District'[Start Date].
Thanks again for your help!
@Anonymous
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!
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
11 | |
9 | |
8 | |
8 | |
8 |
User | Count |
---|---|
13 | |
12 | |
11 | |
10 | |
9 |