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.
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
Solved! Go to 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
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
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.
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
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
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
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
110 | |
91 | |
84 | |
66 |