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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
CatLowe
New Member

Custom Date Table slicer causing data to vanish

Hi,

 

I've been building a report for a client and we need to create a central Calendar table which we can use for filtering purposes and for calculations. I've read up about this online and the process seems to be simple in theory, however after following the step-by-step guide I'm encountering issues.

 

I've created the custom Date table using DAX:

CatLowe_0-1705574440683.png

And have linked it to the two data tables we have, creating Many-to-One relationships from the Created On value of these tables to the Date field in the custom Calendar table:

CatLowe_1-1705574560868.png

The issue comes when I attempt to use the Date field as a Slicer on our report, going from a full table of data to a completely blank one the moment the slicer is moved: (sensitive data blanked out)

CatLowe_2-1705574755040.png

CatLowe_3-1705574792879.png

I understand there's something I've likely missed in the relationships but I'm not sure where to start

12 REPLIES 12
HotChilli
Super User
Super User

This looks like the date table has date/time values with 00:00:00 in the time section and the CreatedOn has various different time values so they won't match up.  Hence relationship doesn't work properly.

--

Date table should have Date type fields.  Fields on both sides of a relationship should be the same data type.

Have updated both to Date but looks to still be occurring:

CatLowe_0-1705593984490.pngCatLowe_1-1705594016295.pngCatLowe_2-1705594043841.png

 

HotChilli
Super User
Super User

Which fields are in the table visual (please be exact here - including any aggregations ) and what fields are in the slicer?

I'm noticing that Created On (please state which table it is from and the relationships it is included in) is a datetime value.

All fields in the table are from the Incident table:
Client - Text field
Property Address - Text field

Created On - Date/Time field

In the slicer we only have Date - Date/Time field, and there is a Many-to-One relationship going from Incident - Created On to Calendar - Date

HotChilli
Super User
Super User

It's difficult to debug this at a distance but I can make some general points:

The data is disappearing from the table visual because there will be one or more measures in the visual which evaluate to blank and powerbi then removes those lines.  This is standard powerbi behaviour.

 

Points to investigate: The combination of fields and measures in the visual and the filters applied on the visual. As I stated above, I don't like the model.  It would be my starting point for investigation : which fields from which tables are in the visual and how are they affected by filters (from slicers and from the relationships between data tables).

 

The interaction between slicers (especially from the same table).

 

You might want to start by reducing the fields in the table visual to only come from the Incident table and try that with one slicer from the date table.  It would prove my theory if any fields/measures added to this table visual from the other table go on to 'break' the visual

Sadly I've tested this as well with as minimalist of a dataset before to the same results. Created a new table with simply Name, Client and Created On, and have a single slicer which is only our new Date table and we see the same disappearance of all records:

CatLowe_0-1705590561187.pngCatLowe_1-1705590608914.png

The majority of the data doesn't have filters applied to it and all tests have been without the other slicers interacting with the table so it should only be filtering by Date.

HotChilli
Super User
Super User

The pictures just posted seem to show that there is no data (fields and measures in the table visual) that meets the filters applied on the visual. I'm especially looking at the date filter between 16/02/2015 and 24/01/2023

What are you expecting to see?

Apologies, I made a mistake with the previous screengrab, however with the updated one we see date is between 13/02/2018 and 26/02/2024, and three records with a Created On of 02/01/2024 have been removed when I'd expect them to remain:

CatLowe_1-1705584759197.png
Oddly if I only modify the higher date then they remain. As soon as the lower date is changed, the records vanish:

CatLowe_2-1705584878304.pngCatLowe_3-1705584919054.png

 

 

HotChilli
Super User
Super User

Right, I see some of what's going on now.  The slicers are affecting each other because they are probably in the same table and that's causing no data to be returned in the main table visual.  You can investigate this by hovering the cursor over the funnel icon in each visual.  That tells you what is filtering the specific visual.

If you are introducing a date dimension in the model then you'll want to use that in the slicer.  I still don't like the model but that's the first problem to solve.

Hi,

There's three slicers built-in currently, Client Reporting Group, Instruction Type and Discipline. Client Reporting Group and Instruction Type both come from Incident and Discipline comes from Account, however none of them have had values selected for filtering when testing with Date, and I've tried removing them and only using the Date filter to get the same result.

Without any options selected on the slicers, there's no filters applied to the table but the other slicers work as I would expect them to, though I did have to make another relationship active as you'd suggested. I can slice down with both incident and account to filter our data down, but adding date into the mix breaks the system:

CatLowe_0-1705582111185.pngCatLowe_1-1705582135654.png

 

HotChilli
Super User
Super User

a visual inspection shows lots of inactive relationships between 2 fact tables.

I don't see a date slicer in any pictures and I can't see what data fields and measures are in the main table visual (and what tables they come from) so can you explain what you are trying to do here?

Hi, the inactive relationships between the 2 fact tables are ones which aren't needed for the report we're building so have been left inactive as there can be errors with creating a looping relationship. There's several times where account and incident are linked in Dataverse, but the Active Relationship is the only one we're currently using as it's the Primary relationship between them.

The slicer is present in the top right of the last screenshot, and it's working from the created on data of the Incident table:

CatLowe_0-1705576595628.png

The end goal is to use the filtered Date table in calculations. Many of the fields in the table are calculated columns which return a value if the Case is within a time frame (i.e. If the Completion Date of the Incident record is within the current month, return 1 otherwise return 0).

This currently works off of two calculated columns I've created to find the first and last day of the current month however the client has requested interest in being able to select the month they're interested in so I need to be able to find the first and last days of a dynamically selected timeframe. The eventual plan for this is to use the list of dates in the filtered Date table, but the first step before that requires getting the Date table established 

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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