Issue with visual not showing some data for specific year, while the measure displays the total

[aasamassa]

I have a table for quotas and a central table for the dates. They are joined by the columns 'quota's year' and 'Date'. Below are sow extracts of the two tables and how they are linked.

Rep Firm	Quota's year	Quota's Value	C_Quota Year
Storer Services	01-janv-23	0	2023
Storer Services	01-janv-22	0	2022
Storer Services	01-janv-25	50000	2025
Storer Services	01-janv-24	50000	2024

Date	Year	QuarterText	YearQuarter
31-déc-2025	2025	Q4	2025-Q4
30-déc-2025	2025	Q4	2025-Q4
29-déc-2025	2025	Q4	2025-Q4
28-déc-2025	2025	Q4	2025-Q4
27-déc-2025	2025	Q4	2025-Q4
26-déc-2025	2025	Q4	2025-Q4
25-déc-2025	2025	Q4	2025-Q4
24-déc-2025	2025	Q4	2025-Q4
23-déc-2025	2025	Q4	2025-Q4
22-déc-2025	2025	Q4	2025-Q4

For the visual, when I use the column 'Year' of the Date tables, I don't see all the values (for example the quota for 2025 does not appear. However, when I just use the column 'Quota's Year' which is from the same table, all the quotas appear. I have put also a picture of the two tables for reference.  The visual on the left uses only data from the 'Quota' table and the visual on the right uses the column 'Year' from the calendar table.

I created a column 'Year' just to make sure that it was referring to the right data but it still did not display the value for the year 2025.

This the formula I am using for the measure : 

What could I do to make all the values appear when I am using the column 'Year'. I need to use that specific column because I have a table using multiple different sources that are all linked by the Date table.

[Ashish_Mathur · Accepted solution]

Hi,

Expand the filter pane to ensure that there is no active filter.  Also, if there is already an active relationship between the Quote year and Dae column, why are you using the TREATAS() functiona at all.  A simple SUM() function should work.

If neither helps, then please share the download link of the PBI file.

[aasamassa]

There was indeed an active filter. For some reason, even though that filter was referring to another table, it was preventing the number to be displayed. 

I added the Treatas() function just to see if it would help, at first i just had the sum() function.

Thanks for your help!

[Ashish_Mathur]

You are welcome.

[ExcelMonke]

This is interesting, and can you confirm that the two columns you are linking on are the same datatype? 

[aasamassa]

Yes they are both linking on a 'Date' format

[srlabhe]

Try changing join to Bi Directional

[aasamassa]

Just tried but no luck, I still have the same issue.

[srlabhe]

Follow below steps for debugging the issue

1. Take a blank canvas in PBI report add Table Visual

2. Identify for which date the data is there in both the tables 

3. Filter Table visual for that date and add both fields from both tables in Visual where you have created the join

4. Add the measure you wanted to see and slowly keep on adding another columns.

You should be able to recreate the issue and knwo which column/or measure has creating it to get data disappear for the date.

All the best 

[ExcelMonke]

You need to connect the columns on the same identifier - so that's to say either connect them both on date (recommended), or connect them on year. I recommend connecting on date because otherwise it will become a many:many relationship which may cause issues further down the line. 

[aasamassa]

Yes they are connected by date. The relationship can be seen here:

The 'Quota's year' which is a date column, is connected to the 'Date' column of the calendar table. Even with that I don't see the right data.