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.
I have reviewed Solved: Lookup value within date range - Microsoft Power BI Community and other related posts of matching a date in one table to a date range in a second table. However, these examples seem to assume that there is another categorical variable that I can use for the initial inner-join. My situation is different.
I have a list of dates:
2019-01-01
2020-01-01
2021-01-01
2022-01-01
and I have a list of intervals
start stop Categorical_Value
2019-01-01 2019-12-31 A
2019-12-31 2020-01-02 B
2019-12-31 2023-01-01 C
2020-01-02 2021-12-30 D
For each date (first list), I want to know which intervals overlap with that list, so for 2019-01-01 I would like a record of the date and null values for start, stop and categorical_value.
Date start stop categorical value
2019-01-01 2019-12-31 2020-01-02 B
2020-01-01 2019-12-31 2023-01-01 C
Appropriate DAX for ths sort of join/lookup completely escapes me.
R
Solved! Go to Solution.
Hi, @busmansholiday
Is this you expected result?
If so, you may need to join the two tables before filtering the data, because it is currently difficult to expand the rows by measure. 'lookupvalue' also does not apply to row data expansion.
Sample:
New Table =
VAR tab =
CROSSJOIN ( List, 'Table' )
VAR tab2 =
FILTER ( tab, List[Date] > 'Table'[start] && List[Date] < 'Table'[stop] )
RETURN
tab2
Best Regards,
Community Support Team _ Eason
Hi, @busmansholiday
Is this you expected result?
If so, you may need to join the two tables before filtering the data, because it is currently difficult to expand the rows by measure. 'lookupvalue' also does not apply to row data expansion.
Sample:
New Table =
VAR tab =
CROSSJOIN ( List, 'Table' )
VAR tab2 =
FILTER ( tab, List[Date] > 'Table'[start] && List[Date] < 'Table'[stop] )
RETURN
tab2
Best Regards,
Community Support Team _ Eason
@busmansholiday , assume the first date is an independent table
then you create a measure like this
new measure =
var _max = maxx(allselected(Date1),Date1[Date])
return
countrows(filter(Table2, Table2[Start Date] <= _max && Table2[End Date] >= _max ))
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
41 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
44 | |
32 | |
30 | |
18 | |
17 |