Forum Discussion

CorneGeyser's avatar
CorneGeyser
Icon for Helper I rankHelper I
3 years ago

Visual Missing Data because of relationship

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):

 

Need Help

11 Replies

  • johnt75's avatar
    johnt75
    Icon for Super User rankSuper User
    3 years ago

    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's avatar
      CorneGeyser
      Icon for Helper I rankHelper I
      3 years ago

      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's avatar
        johnt75
        Icon for Super User rankSuper User
        3 years ago

        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.