Microsoft is giving away 50,000 FREE Microsoft Certification exam vouchers!
Enter the sweepstakes now!Prepping for a Fabric certification exam? Join us for a live prep session with exam experts to learn how to pass the exam. 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!
Check out the April 2025 Power BI update to learn about new features.
Explore and share Fabric Notebooks to boost Power BI insights in the new community notebooks gallery.
User | Count |
---|---|
16 | |
14 | |
13 | |
12 | |
10 |
User | Count |
---|---|
11 | |
10 | |
6 | |
6 | |
6 |