Double date slicer (date table)

[MIKAOKSA]

Hi, 

I have a model with date table created with calendar auto and two different tables I want to filter with this date:

There is a relationship with REP_EMPORATWR_MV[END_ACTUAL] and Dates[date] and with Sheet 2[event date] and Dates[date]. Also both tables are linked with EMPID through DLG_EMPLOYEE_V. I have created a filter which uses the Date[date], but the problem is it doesnt seem to work like it should. For example Sheet 2[EMPWRKHOURS] is not right:

Slicer is set to one day 1.11. to 2.11. so any employee should not have more than 24 hours (in reality 7 to 9 hours), but the values are way off. Previosly I did not have a date table and tried to do this with USERELATIONSHIP but that did not work either. I was sure this model would work but not even close, and I'm stuck. Please help!

BR, Mika

[v-juanli-msft]

Hi @MIKAOKSA 

when slicer is set to 2019/11/1~2019/11/2, it filters 2 days.

Sheet2[EMPWRKHOURS] seems show the correct result as the slicer filters.

What's wrong do you find and what's expected result do you like?

Best Regards

Maggie

[MIKAOKSA]

Hi Maggie!

The problems begin when I try to put them in combined table with the other sheets and filter them both with the same date filter:

This should show the same results as your example which you did but it doesnt.

[MIKAOKSA]

Hi, 

this is still a problem, can anyone help? The data is downloadable above. Everything filters ok when I use tables Date, Sheet 2 and DLG_employee_V but when I try to add something from REP_EMPORATW it just fails to give any results. 

Mika

[v-juanli-msft · Accepted solution]

Hi @MIKAOKSA 

I don't know what should be the correct value for your visual.

When looking into your data model, i find the [END_ACTUAL] is of datetime format, it is wrong to use this datetime column to relate the "date" table.

You need to create a "date" column from [END_ACTUAL] in date format.

You can also change the relationship among "REP_EMPOPERATWR_MV" and other tables to both, then check if it would show correct result.

Best Regards

Maggie

[MIKAOKSA]

It took a long time but yeah, you are correct. Date-values from our ERP always come in a date/time format, so to filter them correctly I first need to make a new column with FORMAT and remove the time. After this filtering with these new values everything works just fine.

BR, 

Mika

[Greg_Deckler]

Tough to say where the problem might be. Could be in a formula, could be something else. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[MIKAOKSA]

Hi, 

it took me a while but here is my sample file to view if it helps figuring out what could be wrong:

https://www.dropbox.com/s/sbdxcny91o3288i/Personal%20efficiency.pbix?dl=0

BR, 

Mika