Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
CorneGeyser
Helper I
Helper I

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:

CorneGeyser_0-1688550712880.png

Excel Source:

CorneGeyser_1-1688550764733.png

Calculated Date Table (There are more columns):

CorneGeyser_2-1688550818827.png

 

11 REPLIES 11
johnt75
Super User
Super User

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.

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.

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.

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

CorneGeyser_0-1688556282042.png

 

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.

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:

CorneGeyser_0-1688557268650.png

 

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

CorneGeyser_1-1688557415719.png

but it is visible on my Data view:

CorneGeyser_2-1688557521365.png

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

CorneGeyser_3-1688557577151.png

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.

I do not have any measures for this visual.

 

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

CorneGeyser_0-1688561319016.png

Also no filters on the page or visual:

CorneGeyser_1-1688561341670.png

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?

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.

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.

CorneGeyser_0-1688563322328.png

I am honestly so sorry.

No problem, glad you sorted it out.

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.