Show Data from date range from 2 date tables with no relationships

[eliasayyy]

hey everyone, im trying to build a dahsboard with a filter page like this

start of week is from a dimdate table called Startdate

end of week is from another dimdate table called enddate

i also have my main dimdate table that has a relationship with my fact table

in this page, i have a chart, this chart is a measure from my fact table a simple sum([facttable[value]) in the y axis and x axis is day name from my main dimdate table

i did this measure

Measure in the Date Selected = 
VAR _start = MIN(StartDate[Date])
var _end = MAX(EndDate[Date])
RETURN
CALCULATE(
    [My Measure],
    REMOVEFILTERS(dimDate),
    filter(dimDate, AND(MIN(dimDate[Date]) >= _start, MAX(dimDate[Date]) <= _end)
    )
)

if i put the measure in a card, i get blank, if i put the measure with any column in my dimdate table, i get blank, however if i put the dimdate[date] column with my measure, it works, the dimdate[date] column must be included for my measure to work, i need to display the measure i na card and show the data by day name but they show blank

now here is if i remove the dimdate[date] column

any help would be appreciated

[GrowthNatives · Accepted solution]

Hi @eliasayyy , you can follow these steps to get your required solution 

1. Keep only one active relationship: dimDate[Date] → Fact[DateKey].
  StartDate and EndDate tables must be disconnected (no relationships to anything). Use them only for slicers.

2.  Axis/rows: fields from dimDate (e.g., dimDate[DayName], dimDate[Date]).
  Never put StartDate/EndDate columns on the visual.

3. Replace your DAX 

Measure in Selected Range :=
VAR _start = MIN ( StartDate[Date] )
VAR _end   = MAX ( EndDate[Date] )
RETURN
IF (
    NOT ISBLANK ( _start ) &&
    NOT ISBLANK ( _end ) &&
    _start <= _end,
    CALCULATE (
        [My Measure],                         -- e.g. SUM(Fact[value])
        KEEPFILTERS ( dimDate[Date] >= _start ),
        KEEPFILTERS ( dimDate[Date] <= _end )
    )
)

KEEPFILTERS preserves the visual’s dimDate[DayName] context.
Filtering the column (dimDate[Date] >= _start / <= _end) avoids the dependency on having dimDate[Date] in the visual.


⭐Hope this solution helps you make the most of Power BI! If it did, click 'Mark as Solution' to help others find the right answers.
💡Found it helpful? Show some love with kudos 👍 as your support keeps our community thriving!
🚀Let’s keep building smarter, data-driven solutions together!🚀  [Explore More]