Creating Date Relationship in Power BI for Slicers

0
votes

I'd like to slice across visuals based on dates in MS Power BI (i.e., one filter for say month will slice all visuals for that time period.

  1. I created a date table from http://www.agilebi.com.au/power-bi-date-dimension/ to link to the date columns in each table.

  2. I connected xlsx workbook. Image of columns in Power BI

  3. I added tried to edit the relationship so connect the 2 date columns. However, when I insert the filter using the date_table as values and try to use it, the visuals disappear. Visuals are Gone

However, when I create the slicer and slice across multiple visuals, they go blank.

**I tried changing the type to "Both" but it didn't fix the problem.

1
powerbislicers
Are you using any measure in the visuals? How did you set the slicers? If you set the Cross filter direction to Both does it keep the same? – alejandro zuleta
Please edit the question so that we have a minimal reproducible example, with some sample csv data and the precise steps you took to create the report. It's kinda hard to see the whole picture at the moment. – Jeroen
Thanks for the comments. I added additional information. – jonjon

1 Answers

0
votes

The script at AgileBI.com.au creates a date table with a 'Date' field that has a data type of text. You can (surprisingly) do a relationship between 2 fields of different data types and not get an error. However, a join between a text field that contains dates, and a regular date field will mean that rows that look like they should match up do not.

You can confirm this is happening by picking a value in the 2 tables that you think should match up and seeing if they are identical. In my case, I had Tuesday, November 01, 2016 in my imported table, and 11/1/2016 in the other table. While they both represent the same date (in the US), they clearly look different. A good place to see this is in the Edit Relationship screen (and it definitely would have been helpful to include a screenshot of that in your original post).

Anyway, if the dates in your table don't match up to the corresponding (text) dates in the Date table, then when you filter to "2016" on your slicer, the visuals will all go blank.

I would recommend:

  1. Click Edit Queries
  2. On the left hand side, select the table of dates (called Invoked Function by the script)
  3. Click on the header for the Date column (it will likely already be selected)
  4. Select Transform on the ribbon
  5. Change the Data Type from Any to Date
  6. Click Home > Close & Apply

Hope this helps.