Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare 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 @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 @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:
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!
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
31 | |
15 | |
14 | |
13 | |
9 |