Visual Missing Data because of relationship

[CorneGeyser]

Hi,

I have a problem with a visual (matrix and line graphs) excluding data that has recently been added to the data source. I ma using an Excel sheet on OneDrive as the source for the data and I have a calculated date table in Power BI. Both tables have a Date ID column (which is a string as "yyyymmdd").

To date the relationship I have between these two tables (using the Date ID) has been working fine, and it still is for all the historical data, but the data that I have recently populated into the Excel source table shows in my data view in Power BI (So I know it exists) but it is excluded in my visual.

I know that the issue is on my relationshio because when I delete the relationship the new data shows in the matrix visual.

What would cause this? I have validated that the data is the same, looks the same and is recognised as the same thing so it is not that they are not equal to eachother.

Below a screenshot of both datasets. My issue is only with Date ID = 20230704.

Relationship:

Excel Source:

Calculated Date Table (There are more columns):

[johnt75]

Check how your date table is calculated, make sure that there isn't a hard coded end date. 

Also, double check that there aren't any filters on your visuals relating to date.

[CorneGeyser]

Here is the calculation to my date table, I am just looking for dates after 31 May 2021:

DimCalendar = FILTER
    (ADDCOLUMNS ( CALENDARAUTO (), 
        "DateID",convert(format([Date],"yyyymmdd"),STRING),
        "Year", YEAR([Date]),  
        "MonthNo", MONTH([Date]),  
        "Month", FORMAT([Date],"mmm"),
        "QuarterNo", FORMAT([Date],"q"),
        "Quarter", FORMAT([Date],"yy \Qq"), 
        "YearMonth", FORMAT([Date],"YYYY-MM"),
        "Weekno_ISO", REPT("0",2-len(WEEKNUM([Date],21)))&WEEKNUM([Date],21), //ISO8601 Weeknum
        "YearWeek_ISO", YEAR([Date])&"-"&(REPT("0",2-len(WEEKNUM([Date],21)))&WEEKNUM([Date],21)), //ISO8601 Weeknum
        "WeekdayNo", WEEKDAY([Date],2), //1-Sun..Sat, 2-Mon..Sat 
        "Weekday", FORMAT([Date],"ddd"),
        "MonthPPE", FORMAT([Date],"mmm"),
        "PPE_Period",IF(month([Date])>5,month([Date])-5,month([Date])+12-5),
        "PPR_Period",IF(month([Date])>2,month([Date])-2,month([Date])+12-2),
        "PPE_FY",IF(month([Date])>5,YEAR([Date])+1,YEAR([Date])),
        "PPE_FYV","FY-"&RIGHT(CONVERT((IF(month([Date]) >5,YEAR([Date])+1,YEAR([Date]))),String),2),
        "PPE_Q", if(MONTH([Date]) in {6,7,8},1,if(MONTH([Date]) in {9,10,11},2,if(MONTH([Date]) in {12,1,2},3,4))),
        "PPE_QV",if(MONTH([Date]) in {6,7,8},"Q1",if(MONTH([Date]) in {9,10,11},"Q2",if(MONTH([Date]) in {12,1,2},"Q3","Q4")))
        ),
    [Date] > DATE(2021, 5, 31))

I have also removed all filters on my visuals previously to confirm that they were not excluding data.

[johnt75]

Try creating a temporary table like

Tmp table =
VAR NewDates =
    CALCULATETABLE (
        VALUES ( '0901-SalesForecast'[DateID] ),
        '0901-SalesForecast'[Index] >= 21
    )
VAR Result =
    CALCULATETABLE (
        VALUES ( 'DimCalendar'[Date] ),
        TREATAS ( NewDates, 'DimCalendar'[DateID] )
    )
RETURN
    Result

where the filter on Index is something appropriate to only include the new data you are having problems with.

If the resulting table is empty or incomplete then there is an issue with the values in the DateID column for the new data. Check for leading or trailing spaces.

[CorneGeyser]

It does return a result, I have validated this as well:

[johnt75]

Try

Tmp table =
CALCULATETABLE (
    '0901-SalesForecast',
    TREATAS ( { DATE ( 2023, 7, 4 ) }, 'DimCalendar'[DateID] )
)

If that produces the correct results then the problem isn't with the relationship, its somewhere else.

[CorneGeyser]

If I use this calculation it does not return anything, but that makes sense because DATE(2023, 7, 4) will return 2023/07/04 00:00:00.

If I change your calculation to the following, it returns the correct result:

Tmp table =
CALCULATETABLE (
    '0901-SalesForecast',
    TREATAS ( { DATE ( 2023, 7, 4 ) }, 'DimCalendar'[Date] )
)

Result:

If it is not the relationship, what could it be? The reason I thought it was the relationship is because when the relatioship is active I do not see it in my visual (below DateID 20230704 is not here):

but it is visible on my Data view:

When I delete the relationship it is visible in my visual:

[johnt75]

The measures you have in the visual must be returning blank. Post the code for the measures and we'll see if we can work out why.

[CorneGeyser]

I do not have any measures for this visual.

The fields I have selected is all from the source table:

Also no filters on the page or visual:

When I create the exact same matrix on a new page it works fine. When I create a new page on the existing page, the error persists?

[johnt75]

Curiouser and curiouser 🤔.

Use Performance Analyzer to get the query generated for both the working version and the non-working version. Have a look at both in DAX Studio and see what the differences are.

[CorneGeyser]

I am so sorry for wasting your time. I have foudn the problem and it is so stupid.

On my dashboard a have a hidden panel that contains slicers, one of them are the financial year and it is set to single select and the previous financial year was currently selected.

I am honestly so sorry.

[johnt75]

No problem, glad you sorted it out.