Forum Discussion
Helper IDouble date slicer (date table)
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
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
7 Replies
- Greg_Deckler
Community Champion
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
MIKAOKSAHi,
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
v-juanli-msftCommunity Support
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
