Forum Discussion

raschdieek's avatar
raschdieek
Frequent Visitor
7 months ago
Solved

Creating date slicer removes data

Problem - when creating a "Date" slicer for information in my visual, I lose data from a table I have set up.   Example: Setting up a slicer based on the Date table, you can see there is a range t...
Need Help
  • raschdieek's avatar
    raschdieek
    7 months ago

    v-hashadapu Thanks for your help. 

     

    I would consider this resolved. I can transform the data that is pulled into the model from DirectQuery as well.  This is suitable for my needs. 

v-hashadapu's avatar
v-hashadapu
Community Support
7 months ago

Hi raschdieek , Thank you for reaching out to the Microsoft Community Forum.

 

This isn’t a slicer or Date table problem. It’s a side effect of using an Import Date table with a DirectQuery orders table. When the slicer is first placed, Power BI may show data without fully pushing the filter to the source. As soon as you interact with the slicer, even without changing the range, Power BI re-runs the DirectQuery and pushes the Date filter through the relationship. At that point, the source only returns data up to 8/21/2019, so anything after that disappears. Power BI is just showing what the source query returns.

 

The clue is the fixed cutoff date. That means the limit already exists on the DirectQuery side, for example in a view, security filter, parameter or a DATE vs DATETIME mismatch. Check the source query and use Performance Analyzer to inspect the SQL after touching the slicer. Once the source returns the full date range under filtering, the issue will go away.

  • raschdieek's avatar
    raschdieek
    Frequent Visitor
    7 months ago

    Thanks for the tip on the Performance Analyzer. 

     

    I'll need to work on my SQL, but when interacting with the splicer, it shows the following:

    EVALUATE
	ROW(
		"MinDate2", CALCULATE(MIN('Date'[Date])),
		"MaxDate2", CALCULATE(MAX('Date'[Date]))
	)

     

    Data listed:

    [MinDate2][MaxDate2]
    Mon, 01 Jan 1900 12:00:00 AMFri, 31 Dec 2049 12:00:00 AM

     

    Return:

    DEFINE
	VAR __DS0FilterTable = 
		FILTER(
			KEEPFILTERS(VALUES('Date'[Date])),
			'Date'[Date] < DATE(2050, 1, 1)
		)

	VAR __DS0Core = 
		CALCULATETABLE(
			DISTINCT('oe_hdr'[order_date]),
			KEEPFILTERS(__DS0FilterTable)
		)

	VAR __DS0PrimaryWindowed = 
		TOPN(501, __DS0Core, 'oe_hdr'[order_date], 0)

EVALUATE
	__DS0PrimaryWindowed

ORDER BY
	'oe_hdr'[order_date] DESC

     

    The return data (partial list below) shows up as all date items with the 12:00:00 AM time stamp associated with each date:

    "Wed, 21 Aug 2019 00:00:00"
    "Tue, 20 Aug 2019 00:00:00"
    "Mon, 19 Aug 2019 00:00:00"
    "Fri, 16 Aug 2019 00:00:00"
    "Wed, 14 Aug 2019 00:00:00"

     

     

    It looks like the issue is that the date ranges passed to the filter are taking a time stamp into consideration.

     

    A time stamp of 12:00:00 AM is added to each date, even though the Date data in the Date Table is the Date data type, and in the Short Date format. 

    The Order Date data in the Order table is also set to Date data type and Short Date format. 

     

     

    • raschdieek's avatar
      raschdieek
      Frequent Visitor
      7 months ago

      For the Order table, I can create a new column by duplicating the date column 

       

      But, when I try to format it to "Short Date" as follows:

      OrderDateNoTime = FORMAT([order_date], "Short Date")

       

      I get the following error:

      "Function 'FORMAT' is not allowed as part of calculated column DAX expressions on DirectQuery models."

       

      I'm altering the table to be Import vs. DirectQuery to see if I can better manage.

      • raschdieek's avatar
        raschdieek
        Frequent Visitor
        7 months ago

        It turns out that changing the table from DirectQuery to Import still didn't do what I needed it to do.

         

        I deleted the table from the model and reloaded it, this time transforming the order Date column to short date format.  

         

        This transform was able to get rid of the underlying time stamp format for me.