Forum Discussion

eliasayyy's avatar
eliasayyy
Icon for Memorable Member rankMemorable Member
11 months ago
Solved

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

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











Need Help
  • GrowthNatives's avatar
    GrowthNatives
    11 months ago

    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]



1 Reply

  • GrowthNatives's avatar
    GrowthNatives
    Icon for Super User rankSuper User
    11 months ago

    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]